texts: Cannot open table mysql/innodb_index_stats

texts:
After a fresh install of XAMPP and import from my linux live db that is working to the windows Dev-stage, I started encountering problems with INSERT. Everything else seems to work well.

The errors I get in the mysql_error.log are:

2013-08-15 12:44:49 16c0 InnoDB: Error: Table "mysql"."innodb_table_stats" not found.
2013-08-15 12:44:49 16c0 InnoDB: Recalculation of persistent statistics requested for table "sizaradb"."pages" but the required persistent statistics storage is not present or is corrupted. Using transient stats instead.
2013-08-15 12:44:59 16c0 InnoDB: Error: Table "mysql"."innodb_table_stats" not found.
2013-08-15 12:44:59 16c0 InnoDB: Recalculation of persistent statistics requested for table "sizaradb"."translations" but the required persistent statistics storage is not present or is corrupted. Using transient stats instead.

I cannot open table mysql/innodb_index_stats from the internal data dictionary of InnoDB though the .frm file for the table exists

I tried to fix it by googling the errors and trying to see fixes of other people, but it is still not working. I have been working on this for 2 days now.

Please help me solve it.


texts: A question regarding MySQL master-slave replication

texts: I want to set up a one-master-one-slave replication. But I have a question. Suppose the read-load is distributed between the master and the slave. I first write to the master and then read from the slave. At this time the information has not reached the slave. So my read will fail. But I have written it! How is such a problem solved or avoided in practice?


texts:
I have installed a Microsoft SQL Server 2012 on a server that also are used as a file server. Each day the server is automatic backed up with Acronis Backup & Recovery to a USB-disc that we switch every day.

My question is whether it is enough to make a system backup, or if I also need to do a SQL Server Backup. Are there any disadvantages to doing like this?


texts: Backups script completes, but doesn't back up all dbs

texts:
I'm running sql server 2005 and I've made a simple backup script that backs up all the databases on one server. The script always succeeds, but when I go to check the log or the files, I see it has only backed up around half of my databases. I've tried it on multiple servers and it does the same thing. I've checked the application log for the backups in the eventvwr, however, it tells me to check the application log for more details so I can never find the detailed information I need.

I saw this similar question and tried the solution that was recommended to try and catch errors. However, there were no errors thrown when I run the script.

DECLARE @db_names NVARCHAR(1000)

DECLARE db_cursor CURSOR
    FOR select name from sys.databases
            where name not in ('tempdb')
    order by name

OPEN db_cursor
FETCH NEXT FROM db_cursor into @db_names
WHILE @@FETCH_STATUS = 0
    BEGIN

    BEGIN TRY

        EXECUTE [Full_Backup] @DB_name = @db_names

    END TRY

    BEGIN CATCH

        EXEC spErrorHandling

    END CATCH

FETCH NEXT FROM db_cursor INTO @db_names
END
CLOSE db_cursor
DEALLOCATE db_cursor

I used this sites example for error handling.

-- Declaration statements
DECLARE @Error_Number int
DECLARE @Error_Message varchar(4000)
DECLARE @Error_Severity int
DECLARE @Error_State int
DECLARE @Error_Procedure varchar(200)
DECLARE @Error_Line int
DECLARE @UserName varchar(200)
DECLARE @HostName varchar(200)
DECLARE @Time_Stamp datetime

-- Initialize variables
SELECT @Error_Number = isnull(error_number(),0),
@Error_Message = isnull(error_message(),'NULL Message'),
@Error_Severity = isnull(error_severity(),0),
@Error_State = isnull(error_state(),1),
@Error_Line = isnull(error_line(), 0),
@Error_Procedure = isnull(error_procedure(),''),
@UserName = SUSER_SNAME(),
@HostName = HOST_NAME(),
@Time_Stamp = GETDATE();

-- Insert into the dbo.ErrorHandling table
INSERT INTO dbo.ErrorHandling (Error_Number, Error_Message, Error_Severity, Error_State, Error_Line,
Error_Procedure, UserName, HostName, Time_Stamp)

SELECT @Error_Number, @Error_Message, @Error_Severity, @Error_State, @Error_Line,
@Error_Procedure, @UserName, @HostName, @Time_Stamp

Is there a reason why backups fail silently? Is there better error handling I can be doing?

[Full_Backup]

declare @path varchar(200)
    set @path = 'P:\Backups\' + CONVERT(char(10), GetDate(),126) + '_' + @DB_name + '.bak'

declare @backupStr nvarchar(max)
set @backupStr = 'BACKUP DATABASE [' + @DB_name + '] TO  DISK = N''' + @path+ ''' WITH NOFORMAT, NOINIT,  NAME = N'''+@DB_name+'-Full Database Backup'', SKIP, NOREWIND, NOUNLOAD,  STATS = 10, CHECKSUM

    declare @backupSetId as int
    select @backupSetId = position from msdb..backupset where database_name=N'''+@DB_name+''' and backup_set_id=(select max(backup_set_id) from msdb..backupset where database_name=N'''+@DB_name+''' )
    if @backupSetId is null begin raiserror(N''Verify failed. Backup information for database '''''+@DB_name+''''' not found.'', 16, 1) end
    RESTORE VERIFYONLY FROM  DISK = N''' + @path + ''' WITH  FILE = @backupSetId,  NOUNLOAD,  NOREWIND'

Edited to add the [Full_Backup] sp


texts: How to limit the range of an insert-update to only look at certain rows/ rows with field value: X?

texts:
I posted something similar before, but am still unsure of the technical implementation --- I'm so close to being there to the finished product.

I have a table of tickets, and a stream of ticket updates sent to that table in an insert/ update fashion.

The problem is with each passing week, or day, etc ..... the table of tickets gets bigger and bigger, making the daily "stream" insert/update lookup take longer, and longer, and longer.

There is a way around this (I think).

Tickets where status = "closed" will never see an update --- they don't have to be looked up by insert/ update, in other words. They are also the vast majority of tickets.

So my question --- how the hell do I prevent a lookup of these tickets?

At first I'm thinking ... add a key .... the first one is simply update where ticket_id = ticket_id .... I could add another ... status <> "closed". But I'm not sure if checking this, and ticket_id, would actually save time. Would it? Or does it matter in which order the update keys are?

Second idea is to have two tables ... an active table, and archive table. Send all the updates through the active table, and then maybe once a day, copy all "closed" ticket rows from the active to the archive, and delete them in the active table. This definitely seems like a massive pain ... but it's a possibility. All lookups would require a table union as well using this option (or maybe view).

I'm not sure ... what do you guys think? Anyone have better ideas or thoughts? Thanks.


texts: missing FROM-clause entry for table in trigger function in postgresql

texts:
I have created an audit trigger for table "product" that should insert data into "log_product".

CREATE FUNCTION public.insert_log_product()
RETURNS trigger
LANGUAGE 'plpgsql'
COST 100
VOLATILE NOT LEAKPROOF
ROWS 0
AS $BODY$
BEGIN
IF (TG_OP = 'UPDATE')
THEN
    INSERT INTO log_table(
      operation,
      product_type_new,
      product_type_old,
      product_name_new,
      product_name_old,
      vendor_id,
      available_quantity_unit_new,
      available_quantity_unit_old,
      delivery_time_in_days_new,
      delivery_time_in_days_old,
      change_date
    )
    VALUES(
      'UPDATE',
      new.product_type,
      old.product_type,
      new.product_name,
      old.product.name,
      new.vendor_id,
      old.vendor_id
      new.available_quantity_unity,
      old.available_quantity_unity,
      new.delivery_time_in_days,
      old.delivery_time_in_days,
      now()
    );

END IF;
RETURN NULL;
END;

 $BODY$;

But I get an error message:

org.postgresql.util.PSQLException: ERROR: missing FROM-clause entry for table "product"

The function is created using PGadmin 4.2.

CREATE TRIGGER insert_log_product_trigger
AFTER INSERT OR DELETE OR UPDATE
ON public.table
FOR EACH ROW
EXECUTE PROCEDURE public.insert_log_product();

The requirement for our audit table is to have each column separately, old and new.


texts:
I have the following questions to ask:

What are the best practices for MongoDB backup
Is mongodump a good option for data greater than 50GB?
Does mongodump have any adverse effect on mongodb while it is backing up?
Is it possible to have incremental backup using mongodump?
How fast is mongodump in backing up a data of size of 50-100 GB?
If at all one uses mongodump to take backup how often should it be performed (Considering the load we'll be adding on DB)?

I understand some of the questions might be very trivial but I'm having a lot of confusion choosing solution for backing up my production database which is currently 60GB large.

Thank you!


texts:
Why are Op Stats all 0's and yet Usage Stats show 29 seeks.  This was returned from Brent Ozar's sp_blitzindex.

Usage Stats                         Op Stats
Reads: 29 (29 seek) Writes:14    0 singleton lookups; 0 scans/seeks; 0 deletes; 0 updates;


texts: Sum data from one table and divide it by count of rows in joined table

texts:
I'm using postgresql. I need to sum data from one table (timeWorked from log table, summed by taskId) and then divide it by the amount of rows in the joined table (task_worker). Also join another table (task) in the process to get description but that should be easy, just keep it in mind.
What would be the most optimal query for such an operation?
So far the only solution i came up is utilising a subquery but i'm wondering if there is a better approach.
Log table:
taskId   timeWorked
1        60
1        90
1        120
2        180
2        210
3        90
3        180
3        90

Task table
id       description
1        making icecream
2        doing salad
3        cooking

Task_worker table
taskId   workerId
1        1
1        2
2        1
2        2
2        3
3        3

My expected output would be something like:
taskId  totalTimeWorked  description
1       135 (270 / 2)    making icecream
2       130 (390 / 3)    doing salad
3       360 (360 / 1)    cooking


texts: DB_ID context from farther up call stack

texts:
In SQL Server, is it possible to get the DB_ID from the context from farther up the call stack?

My goal is to create some handy (and admittedly hacky) utility functions in a dev sandbox database that make it easy and concise to get the fully qualified names of objects given their short or fragmented names, and additionally to delete objects using the same short name. These utility functions would be in a single utility database but called from other  databases on the same server.

From what I can see from testing:

ORIGINAL_DB_NAME() as intended returns whatever was in the connection string, not the current context (set by USE [dbname]).
When called in a function DB_NAME() returns the name of the database where that function is defined. Another way of saying this is that the context inside a function or stored procedure is that of the database in which it is defined

I know the engine keeps track of each of the database contexts up and down the call stack (see below for proof). So is there any way to access this information?

I want to be able to find and operate on objects in the context of the caller's database, even though the executing code is not in the same database. For example:

use SomeDB
EXEC util.dbo.frobulate_table 'my_table'

I know I can just do

EXEC util.dbo.frobulate_table 'SomeDB.dbo.my_table'

But I'm just really curious if it is possible to query the call stack in this way.

Update / note

I did read and download the code from Gabriel McAdams' blog. This provides a record of the calling procedure ID up and down the stack but still assumes everything is in the same database.

Proof the SQL Server remembers DB context up and down call stack

Example: On a dev server with databases TestDB1 and TestDB2

use TestDB1
GO
CREATE FUNCTION dbo.ECHO_DB_NAME() RETURNS nvarchar(128) BEGIN RETURN DB_NAME() END
GO

use TestDB2
GO
CREATE PROCEDURE dbo.ECHO_STACK AS
BEGIN
    DECLARE @name nvarchar(128)
    SET @name = DB_NAME()
    PRINT 'Before, DB_NAME inside dbo.ECHO_STACK : ' + @name
    SET @name = TestDB1.dbo.ECHO_DB_NAME()
    PRINT 'TestDB1.dbo.ECHO_DB_NAME returned     : ' + @name
    SET @name = DB_NAME()
    PRINT 'After, DB_NAME inside dbo.ECHO_STACK  : ' + @name
END
GO

use master
SELECT DB_NAME()  -- Returns 'master'
EXEC TestDB2.dbo.ECHO_STACK

The ECHO_STACK proc prints:

Before, DB_NAME inside dbo.ECHO_STACK : TestDB2
TestDB1.dbo.ECHO_DB_NAME returned     : TestDB1
After, DB_NAME inside dbo.ECHO_STACK  : TestDB2


texts: SQLserver WaitType : EXECUTION_PIPE_EVENT_INTERNAL

texts:
One of our production Microsoft SQL Server 2014 (SP1-CU5)
 experiencing performance issue so I ran the ShortPeriodWaitStats.sql script from Paul S. Randal, SQLskills.com
and the top WaitType is EXECUTION_PIPE_EVENT_INTERNAL it's 50% of all the WaitTypes !
Someone knows something about it ? any tips to track the core pb ?


texts: Commit and rollback in Oracle stored procedure

texts:
In my Oracle stored procedure, I have multiple insert and update statements like below:

create or replace PROCEDURE SPTest
AS
BEGIN
  insert into emptest(empid,empname,deptno)
  (1,'ravi',10);

  insert into test1(id,name,sal)
  (1,'raju',4444);

  update emptest set empname='hari' where empid=1;
END;

If I get any error in any statement, I want to rollback all insert and update statements.

How can I implement commit and rollback in this stored procedure?


texts: SQL Server Snapshot Isolation Level: How to create an Update Conflict?

texts:
Does anyone have an example of how to create an "Update Conflict" using Transaction Level Snapshot  (Not ReadCommmittedSnapshotIsolation)?

Error message example:

  Snapshot isolation transaction aborted due to update conflict. You cannot use snapshot isolation to access table ‘Food’ directly or indirectly in database 'FoodDatabase’ to update, delete, or insert the row that has been modified or deleted by another transaction. Retry the transaction or change the isolation level for the update/delete statement.

The following is not working:

create database FoodDatabase;
alter database FoodDatabase SET ALLOW_SNAPSHOT_ISOLATION ON;

create table dbo.food
(
    FoodId int primary key identity(1,1),
    FoodDesc varchar(255) null,
)

insert into dbo.food
values ('broccoli'), ('strawberry')

Transaction Session 1:
set transaction isolation level snapshot
begin transaction -- do not commit transaction yet
update Food
set FoodDesc = 'Grape' where FoodId = 1

Transaction Session 2:
set transaction isolation level snapshot
begin transaction -- do not commit transaction yet
update food
update Food
set FoodDesc = 'Lettuce' where FoodId = 1


texts: SQL Server 2008 R2 Express high insert slowdown possibly due to PAGEIOLATCH_SH

texts:
I have a program that insert row to a table though multiple connections. For each insert, the connection would open and then close. When I run the program, the insert were fast. However, once it reach about 3000 rows or so, SQL server start slowing down. Eventually it become 30x slower than it should be.

The insert statement is NOT a stored procedure, by simply dynamic sql every time when insert into the database.

This is a SQL Express, so memory utilization is limited. CPU utilization is low when running the insert statements.

If I kill the application and run the process again, the insert will be fast again, for the first few minutes until it become a snail again.

When I ran the following SQL

select r.session_id
,status
,qt.text
, r.statement_start_offset/2
, r.statement_end_offset
,qt.dbid
,qt.objectid
,r.cpu_time
,r.total_elapsed_time
,r.reads
,r.writes
,r.logical_reads
,r.scheduler_id
,r.wait_type
,r.wait_time
,r.last_wait_type
from sys.dm_exec_requests r
cross apply sys.dm_exec_sql_text(sql_handle) as qt
where r.session_id > 50
order by r.scheduler_id, r.status, r.session_id

I notice the query has wait_type of PAGEIOLATCH_SH.

In fact, I notice some other Select statements are having the same problem.

What is PAGEIOLATCH_SH?

What other information should I look for to fix to issue?

Ultimately how do I solve this performance issue?

Thanks


texts: How do I verify the uninstall of Grid Control and WebLogic?

texts:
I installed Weblogic, the required patch and then Grid Control on a Linux (RHEL 5.5) server.  Unfortunately, my instructions did not have the -d64 tag to allow for 64 bit installation and I have to uninstall Grid Control and WebLogic then reinstall.

Their documentation for uninstalling leaves a lot to be desired and I had no less than 5 errors during the process.  My basic question is "How can I verify that I have deleted everything from WebLogic and Grid Control?"  This includes repositories.

Below is a basic overview of what I was given as instructions:

    1. <ORACLE_HOME>/bin/omsca delete -full   Errored

OMSCA-ERR:Admin server is down. Full option requires admin server up

/bin/emctl stop agent    No Error

/sysman/admin/emdrep/bin/RepManager
 -action dropall -dbUser  -dbPassword
 -dbRole  -reposName  -reposPassword
 -mwHome  -mwOraHome  -oracleHome

Could not get above command to work at all

Ran the following:
DROP USER sysman CASCADE;
DROP PUBLIC SYNONYM setemviewusercontext;
DROP ROLE mgmt_user;
DROP PUBLIC SYNONYM mgmt_target_blackouts;
DROP USER mgmt_view;

emca -deconfig all db -repos drop

Manually delete the data files mgmt.dbf and mgmt_ecm_depot1.dbf from the Oracle home of the database.
Done

$/oui/bin/runInstaller -removeallfiles
Done

/etc/rc.d/ directory. For example, /etc/rc.d/rc3.d/S98gcstartup.
No clarity on which files to delete!
Note that instructions show no information on how to uninstall WLS!
Ran:
USER=oracle1 SID=emrep PATH=/home/oracle/Middleware/wlserver_10.3/uninstall
$./uninstall.sh


texts: Will Re-Seeding an Identity column back to 0 cause page splits?

texts:
Simple table with an identity table as a clustered primary key. We unexpectedly hit the max value for the column, so I requested our DBA re-seed the table back to 0 to give us some time to examine updating the column. The table is also purged based on time, so we have a substantial gap in Id's to use up before we collide with anything. Since this is a high insert table, the DBA brought up the potential of page splits since we are no longer inserting at the end of the table.

My question is, how will SQL Server behave when the value of the identity column wraps around? Will inserting into gaps cause a performance issue? I want to consider maybe creating a sequence that will automatically cycle rather that up the id column to bigint.


texts: Free space of tablespace deleting table content (rows)

texts:
I have a theorical question, last week we have found this error in one database. The error has been given by our app when trying to save some WS data.

ORA-01691: unable to extend lob segment RS2.SYS_LOB0000501482C00010$$ by 128 in tablespace RS2QDDA1

In absence of the dba who has de sysdba rights we have a discussion :

If we delete data of some tabla stored in this tablespace, the tablespace will have free space ? And could be back to work for the application.

We questions that because we configure a purge batch to delete old rows from some tables, but it fails in execution time throwing the same error message defined before ORA-01691: unable to extend lob segment RS2.SYS_LOB0000501482C00010$$ by 128 in tablespace RS2QDDA1


texts:
I am trying to learn Oracle database and I am having difficulties on importing a backup file with impdp command. I googled deeply but couldn't find answers to my problems. What have I done until now is:

I created a user as Test1 with tablespace
I granted some privileges through sqlplus
I imported the backup file by using impdp command with options fromuser=Test2 touser=Test1
I imported the backup file by using impdp command with options fromuser=Test2 touser=Test1
I started sqldeveloper and connected to Test1
I expanded tables but there is no table to be displayed
I expanded other users. There is also another Test1 among other users
I checked tables of Test1 and the tables were all there

Now my questions:

Although I chose touser=Test1 option, why the tables are displayed in Test1 which is in other users group? I expected the tables to be in first users' table list
How can I import database directly to the user I created? I don't want to use other users because I have to use [schema_name].[table_name] in my selects which is a waste of time for me.
I can click on tables in other user table list and display the data but I cannot open a blank sql page to write some queries. Is there any way to do that?

EDIT:

The command I used when importing is:

impdp Test1/123 DIRECTORY=backup_dir DUMPFILE=Test.dmp FULL=Y fromuser=Test2 touser=Test1

The result is as follows:


texts: What is your workflow for planning a data migration?

texts:
So many times I've been brought in at the end of a software development effort and been told something like "okay, we've got all this new code and it requires tables to change and data to be migrated".

It seems like every time it's a one-off, shoot-from-the-hip, best-guess scenario. I feel like this is my weakest skill set as a DBA.

I'd like to get into some patterns for approaching, managing and testing data migrations.

Please clue me in to some best practices and/or where I can get learning material to help me get better in this area.


texts:
I have a table with the following columns

member_number
step_count
social_activity -- (which contains both Challenges and Posts)
high_fives

Basically all the columns 2-4 are counts for members.

I want to create a report where I can display member_number, step_count, social_activity (where it is 'challenges'), social_activity (where it is 'posts') and high_fives.

I wrote the query

select member_number, step_count, social_activity
from ABC
group by member_number

But I want the query to get the separate count for social_activity ('challenges') and social_activity ('posts').

Can some one help me with this?


texts: Best way to write SQL Query that checks a column for non-NULL value or NULL

texts:
I have a SP with a parameter that has NULL as default value and then I want to do a query like this:

SELECT ...
FROM ...
WHERE a.Blah = @Blah AND (a.VersionId = @VersionId OR (@VersionId IS NULL AND a.VersionId IS NULL));

The WHERE above checks for both a non-NULL value and a NULL value for @VersionId.

Would it be better in terms of performance to instead use an IF statement and duplicate the query into one that searches for non-NULL and another for NULL like so? :

IF @VersionId IS NULL BEGIN
    SELECT ...
    FROM ...
    WHERE a.Blah = @Blah AND a.VersionId IS NULL;
ELSE BEGIN
    SELECT ...
    FROM ...
    WHERE a.Blah = @Blah AND a.VersionId = @VersionId;
END

Or the query optimizer makes it essentially the same?

UPDATE:

(Note: I am using SQL Server)

(And as far as I know, using a.VersionId = @VersionId for both cases will not work, will it?)


texts: How often is Postgres parsing a foreign table based on file_fdw?

texts:
I have a foreign table defined with file_fdw, which reads the data from a CSV file. Postgresql parses the CSV file and converts the date for example from YYYYMMDD to YYYY-MM-DD. This works fine.

My question is how often gets the CSV file parsed? Does it get parsed on every select? Or does it get cached? And if so how long does it get cached?


texts: PostgreSQL versus MySQL for EAV structures storage

texts: Is there any practical difference or what are the advantages of using Postgres 9.x versus MySQL 5.x for storing EAV (Entity-Attribute-Value) structures, especially in terms of read performance?


texts: Group by + Order by Behavior Explanation?

texts:
Suppose I have:

Product(_id,Name)

Provider(_id,Name)

Provides(ProductId, ProviderId, Price)

If I want to obtain the providers who sell the most expensive products, and whose name starts with A.

Could you tell me how the following snippet runs? It seems to be
returning the results I ask for.
Does group by get a random record from the table ?
Is Order by working first and then Group by gets the first record?

SELECT provider.Name, piece.Name, provides.Price
FROM provider,piece,provides
WHERE provider._id = provides.ProviderId
AND piece._id = provides.PieceId
AND provider.Name LIKE 'A%'
GROUP BY piece.Name
ORDER BY provides.Price DESC

Edit1: I still haven't gotten answered if the way this query is made, my question (bold text) is answered. I'm ALSO asking for what happens once/behind the scenes Group by is applied. See I'm trying to order based on price. I'm not asking about the basic idea, but what's going on behind.
I got answered "No." So what happens behind the Group by and over who Order by works over?

Edit2: Seems the question was a bit vague: The original question, for clarification, meant:
For every product, I want the provider who sells it the most expensive.

So can you guys suggest solutions with standard SQL?
What was on my mind, along with the results giving me the right things was:

So, does anyone know why group by doesn't internally keep the records "grouped" and then order by sorts them? and shows instead an arbitrary record? (meaning for this problem the solution I had in mind is not trustworthy/predictable).


texts: How to select only the number from "show global status like 'Com_insert';"

texts:
The title says it all.

How can I select only the number result from show global status like 'Com_insert' ?


texts:
I have SQL Server 2000 and ever since upgrading from SP2 to SP4 random errors began happening.  Mostly overflow errors, sometimes in SqlDateTime and sometimes in data type numeric.

For example, right now I have a column with 1099511628.776 as its value, the column type is decimal(18,3) which can hold up to 10^38, so clearly this is not an overflow, however when I try to update that column and set it to 1 I get the error Arithmetic overflow error converting numeric to data type
numeric.

I ran DBCC CHECKDB on the database and I got 6 consistency errors in two tables, for the first table:

Msg 8970, Level 16, State 1, Line 1
Row error: Object ID 578101100, index ID 0, page ID (1:24157), row ID 61.
Column 'wh_id' was created NOT NULL, but is NULL in the row.

Second table:

Msg 8928, Level 16, State 1, Line 1
Object ID 933578364, index ID 0: Page (1:7160) could not be processed.
See other errors for details.

Msg 8939, Level 16, State 12, Line 1
Table error: Object ID 933578364, index ID 0, page (1:7160).
Test (*(((int*) &m_reservedB) + i) == 0) failed. Values are 0 and 65536.

Msg 8944, Level 16, State 2, Line 1
Table error: Object ID 933578364, index ID 0, page (1:7223), row 12.
Test (!(hdr->r_tagA & (VERSION_MASK | RECTAG_RESV_A | RECTAG_RESV_B))) failed.
Values are 17 and 193.`

Msg 8928, Level 16, State 1, Line 1
Object ID 933578364, index ID 0: Page (1:7254) could not be processed.
See other errors for details.`

Msg 8939, Level 16, State 106, Line 1
Table error: Object ID 933578364, index ID 0, page (1:7254).
Test (m_freeCnt == freeCnt) failed. Values are 2442 and 2444.`

Now for the first error, I tried running the query:

SELECT *
FROM table1
WHERE wh_id IS NULL

However, it returns zero rows. For the rest of the errors, I don't know how to query the table to find the the bad row(s).

How can I fix these consistency errors, and are they the reason for the random errors?


texts: Is there an ACCESS 2007 JET SQL editor or client (not sure the right term is) that can be used to code Access Jet SQL queries? It is very difficult to write SQL queries in ACCESS 2007 since it's like basically typing in notepad without any special text formatting for keywords or commands for example. Its hard to write and read... Any suggestions or tools that I can use to make this process easier?


texts: Query: select all rooms which are not booked

texts:
I have a simple room booking system consisting of following tables:

booking
| id | user | begin | end |

room
| id | name |

booking_map
| id | room_id |

I can list all rooms that are booked at the moment with the following query:

SELECT booking.id,booking.user,booking.begin,booking.end,room.name
FROM booking,booking_map,room WHERE ((booking.id = booking_map.id)
AND (room.id = booking_map.room_id) AND (begin < now()) AND (end > now()));

But how would I list all rooms that free?


texts: Relational Model Design - Entity with one attribute

texts:
I am trying to design a database relational model for an assignment. First, I design the ER diagram, and then the relational model with tables (schemas).

Here's my question: Is it possible for an entity to have only one attribute?


texts: SQL Server job failure email without Internet connection

texts:
We are using a local area network and we send emails through Outlook via Exchange Server.

My question is can I get SQL Server job failure notification emails in Outlook via LAN (without an Internet connection)? And if yes, how?


texts: Designing associations where no obvious relationship

texts:
I hope this question is in the appropriate place.
I'm trying to work out the best way to design associations between two tables with no common id's at present. I have them referencing table names and column names. I need to know if this approach is ok or if there is a better design I can consider.
I have 2 tables: exercise and habits.
In habits, I have defined goals (rows) for each habit:

In my exercise table, I post daily updates:

I am using Sequelize & Node. Hence I planned to use the operator column to inform how the function should work (eg, sum vs average), and dynamically read the table and column from which to retrieve the metric.
One solution would be to include a rolling mean of the metric, eg a rolling mean of the exercise minutes in the exercise table, then the value is always available, but redundant if i'm not tracking that habit.
Another way I thought of is to include a column in exercise that has an array of habit id's. So the column values would be [5,6] here. That would work through post-find hooks in Sequelize but again, feels like I should define a more direct association at the start.
Ideally the solution would be dynamic, so refer to any table, any target column, operator, sum or avg...etc as i'll be referencing other tables (sleep, drinks etc).


texts: How to replace and overwrite all existing objects in Oracle with impdp for full database import?

texts:
I want to replace all the existing objects in my database, not just tables, into my current database.

I ran expdp with the parameter full=yes as the system user, who I granted datapump_imp_full_database while logged in as sysdba, from the instance I am importing from. I ran impdp on the target database with the parameter table_exists_action=replace, but (understandably) only existing tables were replaced, but procedures, functions, views were not.

Is there an equivalent of table_exists_action for all objects? If not, how can I achieve this?

Both databases are Oracle 12c on Windows 10.


texts:
I've recently installed PostgreSQL on my PC as a requirement for my DB class, and as a practice, I decided to try and setup a remote connection for it so I can access it via online services. But after hours of searching the internet and dba.stackexchange and trying different configurations, I still can't get it to work. Here are some of the things I've tried:

Added listen_addresses = '*' in the postgresql.conf file
Added the following lines to the pg_hba.conf file:

# Allowing IPv4 remote connections
host all all 0.0.0.0/0 scram-sha-256

# Allowing IPv6 remote connections
host all all ::/0 scram-sha-256

Restarted the service via the pg_ctl restart command. The logged outputs:

2021-04-30 09:23:45.821 +0430 [12108] LOG:  starting PostgreSQL 13.2, compiled by Visual C++ build 1914, 64-bit
2021-04-30 09:23:45.825 +0430 [12108] LOG:  listening on IPv6 address "::", port 5432
2021-04-30 09:23:45.826 +0430 [12108] LOG:  listening on IPv4 address "0.0.0.0", port 5432
2021-04-30 09:23:45.971 +0430 [7276] LOG:  database system was shut down at 2021-04-30 09:23:45 +0430
2021-04-30 09:23:46.037 +0430 [12108] LOG:  database system is ready to accept connections

Checked the pg_ctl status command's output:

pg_ctl: server is running (PID: 12108)
C:/Program Files/PostgreSQL/13/bin/postgres.exe

And finally when entering my credentials for the web service I am trying to connect from, I made sure to enter the IP that whatsmyip gave me for the Hostname field.

I am all out of ideas on what to do. Is there a step that I am missing? Since I'm a beginner, I may have made a silly beginner's mistake (like entering the wrong username!), though I did check my credentials multiple times and I don't think there's anything wrong with them.
I am running PostgreSQL 13.2 on Windows 10.
The web service I am trying to connect to my local DB is Deepnote's PostgreSQL integration. Are there any other ways that I can try to see if my configuration works?
I added a new rule in the Windows Defender Firewall > Inbound Rules section to allow TCP connections on port 5432.
Then I restarted the server with pg_ctl restart and ran the command netstat -a | sls 5432 (PowerShell) which returned the following output:
TCP    0.0.0.0:5432           MyPCName:0        LISTENING
TCP    [::]:5432              MyPCName:0        LISTENING

But I still get the error message:
Could not connect to server: Connection timed out
Is the server running on host "MyIPAddress" and accepting
TCP/IP connections on port 5432?

This is the exact error I get, as requested in the comments.


texts: How to show missing rows with default values

texts:
I have a table Category which has a few missing records for a few months. The table looks like this

Category    Month       Amount
Opt         January     12.00
Opt         February    0.00
Opt         March       -1042000.00
Opt         April       0.00
Opt         May         0.00
Opt         June        0.00
Opt         July        -782663.00
Opt         November    0.00
Opt         December    0.00

This table has missing rows for a few months. I would like to query and show this data as well.

So I created a month table

MonthNameLong   MonthNameShort
January         Jan
February        Feb
March           Mar
April           Apr
May             May
June            Jun
July            Jul
August          Aug
September       Sep
October         Oct
November        Nov
December        Dec

So that I can do a right join on this and get the missing details as well. The query I wrote is

select s.Category, m.MonthNameLong, coalesce(s.Amount, 0.0) as Amount
from Category s
RIGHT JOIN Month m
ON s.Month = m.MonthNameLong

And the result I get is

Category    MonthNameLong   Amount
Opt         January         12.00
Opt         February        0.00
Opt         March           -1042000.00
Opt         April           0.00
Opt         May             0.00
Opt         June            0.00
Opt         July            -782663.00
NULL        August          0.00
NULL        September       0.00
NULL        October         0.00
Opt         November        0.00
Opt         December        0.00

But instead of NULL category is there a way I can get Opt as the category.

I have attached the fiddle here https://www.db-fiddle.com/f/7sycoGZ4sYxNzdYeTvJauC/0

This is very similar to SQL join query to show rows with non-existent rows in one table question but that has all the data in the table so there isn't missing rows.


texts:
I've been using xp_delete_file to delete old backup files but I now an a need to delete files that are part of an application (as part of GDPR). As xp_delete_file only will only let you delete log or backup files, I thought I'd write my own in C#.

The trouble I'm finding now is extended stored procedures can only be rolled out to the master database. I'd rather contain this new stored procedure in my own user db so I can distribute via source control etc.

Does anyone know a way round this without using xp_cmdshell?

It could be done in PowerShell. I could even write the entire thing in C#. It however be really handy to be able to do it in T-SQL as well.

I am open to using a CLR stored procedure that invokes C# code. I was thinking of changing the stored procedure to a function if I can't deploy a stored procedure.


texts:
I've created a large database on my local WAMP webserver.

I would like to send that database with all tables and table data to another computer.

Is this possible?

mySQL ver: 5.5.15
PHP ver: 5.3.8
Apache ver: 2.2.21

Thanks


texts: Agent Job: Execute T-SQL on different server?

texts:
While migrating SQL Server databases on updated hardware/software we separated the ETL server from the database server (referred this link).

We were moving our SSIS packages and agent jobs on ETL server. But the T-SQL steps in agent jobs need databases to be present on same server as well.

How do we proceed from here, do we create databases on the ETL server OR move SSIS packages only on ETL server and keep agent jobs on database server?


texts:
I have a fairly large (16 vCPU / 47 GB of RAM) linode box which hosts a running MySQL Server. This box currently does nothing else (and we are not planning to install anything else on it for now).

The server is monitored by New Relic. Our warning policy places the server in RED (=danger) state when either : CPU > 80% or Memory > 90% or Disk IO > 90% or Disk Full > 90%.

The DB is fairly large (lots of data!) but low access rate (< 1 request per second). We are basically collecting lots of data, and we will do something out of it "someday". I am not complaining about the DB perfs. The CPU usage is flat around 1 or 2 %. No issue there either.

Should I still be worried about the memory warning or should I modify the server warning policy to "untrack" memory usage ? Or should I simply force mysql to use less memory (via a config somewhere).

Is there any relevant measure you think I should keep an eye on. As you guessed I have little to none dev ops / admin experience.


texts:
In our production DB, we only have 1 tempdb file and it had ballooned to 180GB.

I ran:

Alter Database tempdb
modify file (name='tempdev', size=30gb);

to resize the primary file, and then ran additional:

ALTER DATABASE tempdb
ADD FILE (NAME='tempdev2', FILENAME='M:\SQLData\tempdb2.ndf', Size=30GB);

to add additional tempdb files. I ran into a drive space error,
as the primary did not shrink to 30gb and the operation was halted mid-way.

I am now left with one additional tempdb file that is 30GB while the primary is 180GB and I am unable to run get rid of it.

I have tried running

DBCC SHRINKFILE('tempdev2', EMPTYFILE)

but I get an error:

  Could not locate file 'tempdev2' for database 'tempdb' in sys.database_files.
  The file either does not exist, or was dropped.

I feel this error is because the operation was not able to complete successfully. (Do I have corruption?)

And when I try to run

ALTER DATABASE tempdb
REMOVE FILE tempdev2;

I get the following error:

  The file "M:\SQLData\tempdb2.ndf" has been modified in the system catalog. The new path will be used the next time the database is started.
  Msg 5042, Level 16, State 1, Line 35
  The file 'tempdev2' cannot be removed because it is not empty.

The sys.database_files looks like this

Results from tempdb.sys.all_objects

Sys.database_files

Edit:
I was able to resolve the 'file not found' filename corruption that i was receiving when trying to run the EMPTYFILE Shrink. I used the File ID instead of the logical name.

DBCC SHRINKFILE(3,EMPTYFILE); -- File ID instead of logical name.

Another method I found that worked was renaming the file.

ALTER DATABASE tempdb MODIFY FILE (NAME =tempdev2, NEWNAME =tempdev3);


texts: grant execute on utl_file to user in Oracle 11g XE

texts:
sqlplus connect <sys>/<pwd>
grant execute on utl_file to <user>
connect <user>/<pwd>
DESC utl_file

then i get:
ORA-24372: invalid object for describe

How can I access to UTL_FIL from other users in 11g xe? I already tried with public as well


texts: How to get count of multiple tables from linked and local servers

texts:
How can I get records count of multiple tables from linked servers.

I have two servers named production and local. I need to write query to get result as mention in this picture.

I tried

SELECT 'TableA' AS Tables, COUNT(*) AS 'QA' FROM [Database].[schema].[tablename]
UNION
SELECT 'TableB' AS Tables, COUNT(*) AS 'QA' FROM [Database].[schema].[tablename]

How to display records count of linked server?


texts:
I have two tables with same structure I need to copy over all tables from table A to table B, the problem is that some records from table A already exist in table B so that made the Import Fail.

So I made a query to do the import (Also using the build in Import tool) like that

SELECT * from TransactionEntryN
WHERE TransactionEntryN.TransactionEntryID
NOT IN (select TransactionEntryID FROM TransactionEntry)

The problem is that this operation takes 13 min. to copy just 50K records and I have 16 Million records there, it would take me a week to finish that...

Is there any faster way to do it?

btw the primary key TransactionEntryID is a uniqueidentifier that may slow it down? (I can't change it I'm just wondering if that the issue.


texts: What is the best practice location for Memorize Optimization (OLTP) files?

texts:
Best practices for log and mdf files is to split across drives (e.g. G:\SQL Data & I:\SQL Logs).
What are the best practices for memory optimization files? Should they reside on their own drive? Can they reside on a Azure Temp drive (better IO performance but files will be lost on redeploy, tempdb can be hosted here)?
I am still unclear on the purpose of the Memory Optimized file set and what is actually stored there and the rate of IO.


texts: multisubnetfailover=yes and registerallipprovider = 0

texts:
I have a multi-subnet availability group. The application connection string specifies
multiSubnetFailover=Yes
And availability group listener network has parameter set
RegisterAllProvidersIP=0
Application uses ODBC 13.1 which does support multisubnetfailover.
When RegisterAllProvidersIP=0, do we have to remove multiSubnetFailover=Yes parameter from the application connection string?
Would also like to understand how this would work from an application perceptive with multiSubnetFailover=Yes.
resolve-dnsname will return 1 ipaddress for the listener.
Application connections attempts to IPs will not be sequential when multiSubnetFailover=Yes. with just 1 IP is recorded in the dns, the connection should be ok. Is this right way of understanding ?


texts: Postgresql 10: Can not establish DB connection

texts:
I have created a new database and user in Postgresql 10 named 'reportingdashboardproject' and 'repadmin' respectively. I used the following commands to create and grant privileges:

postgres=# create database reportingdashboardproject;
postgres=# create user repadmin with encrypted password '<password>';
postgres=# grant all privileges on database reportingdashboardproject to repadmin;
psql=# grant all privileges on database reportingdashboardproject to repadmin ;

I get this in response when trying to start my Express.js server with a db connection URI using the above:

FATAL password authentication failed for user repadmin

Here is the relevant code:

const dbConfig = {
  operatorsAliases: false
};

const Sequelize = require("sequelize");
const sequelize = new Sequelize('postgres://postgres:postgres@localhost:5432/reportingdashboardproject', dbConfig); //also tried using repadmin:<password>, failed.

I get the same error when trying to open the connection through DBeaver (port 5432)

I then tried using user: postgres and password: postgres, or empty. None of these combos worked. On the empty password attempt I saw this:

The server requested password based authentication, but no password was provided

Partial Db list (template0 and template1 were there too):

postgres=# \l

  Name            |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges
---------------------------+----------+----------+-------------+-------------+-----------------------
 postgres                  | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
 reportingdashboardproject | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =Tc/postgres         +

User list:

postgres=# \du
                                   List of roles
 Role name |                         Attributes                         | Member of
-----------+------------------------------------------------------------+-----------
 postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
 repadmin  |                                                            | {}

I have tried explicitly granting privileges to the superuser also:

postgres=# grant all privileges on database reportingdashboardproject to postgres;
GRANT

Could it be a sudo/permissions issue of some kind?

postgres=# \q
could not save history to file "/opt/PostgreSQL/10/.psql_history": Permission denied

Not sure what to troubleshoot next. Thanks for any guidance.

EDIT: Adding the following:

Inside /etc/postgresql/10/main/pg_hba.conf I see this:

# TYPE  DATABASE        USER            ADDRESS                 METHOD

# "local" is for Unix domain socket connections only
local   all             all                                     peer

# IPv4 local connections:
host    all             all             127.0.0.1/32            md5

# IPv6 local connections:
host    all             all             ::1/128                 md5

# Allow replication connections from localhost, by a user with the
# replication privilege.
local   replication     all                                     peer
host    replication     all             127.0.0.1/32            md5
host    replication     all             ::1/128                 md5

.

postgres=# select * from pg_authid where rolname='repadmin' \gx
-[ RECORD 1 ]--+------------------------------------
rolname        | repadmin
rolsuper       | f
rolinherit     | t
rolcreaterole  | f
rolcreatedb    | f
rolcanlogin    | t
rolreplication | f
rolbypassrls   | f
rolconnlimit   | -1
rolpassword    | md529562c8706ed74d108795a4e9a37f261
rolvaliduntil  |


texts: Server and Database role memberships required to add columns and constraints

texts:
I have a user with the following server and database role memberships:

securityadmin (server)
public (server)
db_owner (database)
db_accessadmin (database)
db_securityadmin (database)

When running the below script in SQL 2005, it runs through without any issues:

BEGIN TRAN
IF NOT EXISTS (SELECT COLUMN_NAME
               FROM INFORMATION_SCHEMA.COLUMNS
               WHERE TABLE_NAME = 'Opfix'
                 AND COLUMN_NAME = 'ModifiedBy')
BEGIN
    ALTER TABLE [OpFix] ADD [ModifiedBy] [int] NOT NULL
    CONSTRAINT DF_OpFixModifiedBy DEFAULT ([dbo].[fnSoftmarLoginUserID]())
END
ROLLBACK TRAN

When running the same script on SQL 2008 and SQL 2012, I get the below error:

  Cannot find the object "Opfix" because it does not exist or you do
  not have permissions.

So, my questions are:

Does it makes sense that I get different results between SQL 2005 and SQL 2008+, with the same update and the exact same user permissions?
What are the minimum requirements for this script to run through?
The only way I could get this script to run through on SQL 2008+ was to grant sysadmin membership, is that as expected?


texts:
I want to update the ProductStock field in tblProduct when this query is run. The ProductStock should minus the Quantity field in tblOrderProduct. (ignore the * sign, I just wanted to see if multiplication worked). The user should be prompted to enter the OrderId beforehand
Thanks


texts: How do you create a view with SNAPSHOT_MATERIALIZATION in SQL Server 2017?

texts:
SQL Server 2017 has a couple new stored procedures:

sp_refresh_single_snapshot_view – input param for @view_name nvarchar(261), @rgCode int
sp_refresh_snapshot_views – input param for @rgCode int

And new entries in sys.messages:

10149 – Index that has SNAPSHOT_MATERIALIZATION cannot be created on view ‘%.*ls’ because view definition contains memory-optimized table(s).
10642 – SNAPSHOT_MATERIALIZATION cannot be set for index ‘%.*ls’ on ‘%.*ls’ because it is only applicable to indexes on views.
10643 – SNAPSHOT_MATERIALIZATION cannot be set for ‘%.*ls’ on ‘%.*ls’ because it is only applicable to clustered indexes on views.
10648 – SNAPSHOT_MATERIALIZATION cannot be set for partitioned index ‘%.*ls’ on ‘%.*ls’.
10649 – Nonclustered index ‘%.*ls’ cannot be created on ‘%.*ls’ that has clustered index ‘%.*ls’ with SNAPSHOT_MATERIALIZATION.
10650 – Refresh of snapshot view(s) requires snapshot isolation to be enabled on the database.
3760 – Cannot drop index ‘%.*ls’ on view ‘%.*ls’ that has SNAPSHOT_MATERIALIZATION.
4524 – Cannot alter view ‘%.*ls’ because it has snapshot materialization.
4525 – Cannot use hint ‘%ls’ on view ‘%.*ls’ that has snapshot materialization before the view is refreshed.

And new Extended Events:

So how can we create a snapshot-materialized view? (Microsoft hasn't documented it yet, obviously.) Here's a gist with things I've tried so far that haven't worked.


texts: SQL Full Backup job is taking too long to complete

texts:
I am facing a strange issue with daily FULL backup. We have a total of 23 databases. We have 1.21 TB big database in this SQL server 2012 Standard Edition. We are taking daily FULL database backup for all databases.

For the last 2 days the backup is taking too long. After more than 10:30 hours it had only completed 67 % of the backup. Daily Full backup job completion time is normally 4 to 5 hours.

And the backup job is also showing suspended state.
I found last_wait_type is ASYNC_IO_COMPLETION for this backup job.

We have sufficient disk space available.

Can anyone make any suggestions on this issue?


texts:
For a DR test, I've tried to split the slave from the master.  To do this, I edited the master postgresql.conf, turned off archive_mode and restarted.  No problem.

Now I'm trying to make the slave a master, so I added recovery_target_time to recovery.conf, ensured that archive_mode is off in the postgresql.conf file, and tried to restart.

This failed.  (The command scrolled off the screen, so I can't show the results.)

pg_ctl stop -D

This worked:

pg_ctl stop -D /var/lib/pgsql/data -m immediate

So I tried to restart:

[postgres@stdrcdsdb202 ~]$ pg_ctl start -D /var/lib/pgsql/data -w
waiting for server to start....
LOG:  could not create IPv6 socket: Address family not supported by protocol
LOG:  database system was interrupted while in recovery at log time 2017-08-23 23:22:55 EDT
HINT:  If this has occurred more than once some data might be corrupted and you might need to choose an earlier recovery target.
LOG:  starting archive recovery
LOG:  restore_command = '/usr/bin/pg_standby -l -d -s 2 -t /tmp/pgsql.trigger.5432 /var/lib/pgsql/backups/wal_archives %f %p %r 2>>standby.log'
LOG:  recovery_target_time = '2017-08-23 22:30:00-04'
FATAL:  the database system is starting up
.FATAL:  the database system is starting up
.FATAL:  the database system is starting up
(snip)
.FATAL:  the database system is starting up
.FATAL:  the database system is starting up
.FATAL:  the database system is starting up
.LOG:  restored log file "0000000100003022000000B0" from archive
LOG:  automatic recovery in progress
LOG:  redo starts at 3022/B0D18388, consistency will be reached at 3022/B1000000
FATAL:  the database system is starting up
.FATAL:  the database system is starting up
.FATAL:  the database system is starting up
(snip)
.FATAL:  the database system is starting up
.FATAL:  the database system is starting up
.FATAL:  the database system is starting up
.could not start server

But...

[postgres@stdrcdsdb202 ~]$ pg_ctl status -D /var/lib/pgsql/data
pg_ctl: server is running (PID: 5669)
/usr/bin/postgres "-D" "/var/lib/pgsql/data"

[postgres@stdrcdsdb202 ~]$ psql
FATAL:  the database system is starting up
psql: FATAL:  the database system is starting up

Any hints or solutions?

Thanks


texts:
I have a JSONB object with many elements, one of which is a date.  The following query is now very slow with millions of rows in my table:
SELECT * FROM table WHERE obj->>'dob' BETWEEN '2019-01-01' AND '2020-01-01'.

How can I improve performance on this query?


texts: Database design: User table and relationships

texts:
I'm designing a DB for a small school project, but currently struggling with the design of the User table and it's related tables.

Basically:

A User can be a Student, a Teacher or an Admin.
Students, Teachers and Admins share some common data (name, last_name, login dates) but each type has unique information (Teachers have a professional license; Students have one Carrer associated to their profile, and so on).

So I'm stuck with two designs. The first one is as it follows:

This aproach allows me to have the unique fields (not listed in the image) in each type table, but the problem lies with the field duplication.

The second approach would be to have a single User table and a second User_data table (instead of the three separated tables in the first design), but I think that would create another set of problems, like having NULL values if the current register is a Student, Teacher or Admin.

Any suggestions on how to improve this design?


texts:
I have Windows Server 2008 with SQL Server 2012 Enterprise installed on it.

I disabled the Windows firewall and I'm sure that there isn't another firewall.

I cannot telnet my server.

I enabled TCP/IP, named pipes etc. in the SQL Server Configuration Manager and restarted everything.

By the way I can telnet locally, the problem is telnet via the network.

Any idea why I cannot telnet?


texts: Someone disabled lot of SQL Server logins on one of our SQL Server 2014 boxes yesterday, causing a major outage for some of our applications. I need to find out who disabled those logins. Is there a way to find this? I checked the SQL Server logs, Event Viewer logs, and the default trace, but didn't find anything helpful.


texts: Dropping duplicate|redundant Unique Constraint from FILESTREAM table

texts:
I have a table with a FILESTREAM column, and it has two unique constraints specified for the same FILESTREAM column, ie:

ALTER TABLE [dbo].[TableName]
    ADD CONSTRAINT [UQ__TableName__33C4988760FC61CA]
    UNIQUE NONCLUSTERED ([GUID_Column]);
GO

ALTER TABLE [dbo].[TableName]
    ADD CONSTRAINT [UQ__TableName__33C49887145C0A3F]
    UNIQUE NONCLUSTERED ([GUID_Column]);
GO

I'd like to drop one of the unique constraints, as they are duplicates.  However, when I try and drop one of the two duplicate constraints, I receive the following error.

  A table with FILESTREAM column(s) must have a non-NULL unique ROWGUID column.

Anyone know how to remove one of the two constraints?


texts: If the users need INSERT/UPDATE/DELETE permissions, is Windows auth still more secure than SQL Server auth?

texts:
Some background first:

The problem described below wouldn't exist at all if the database in question would have been built with a DBA's mindset:
Data access only through views and stored procs --> the user is able to use the application without having any permissions on the table.

But the database in question was built with a "developer's mindset", so the app sends INSERT, UPDATE and DELETE queries to the server --> it needs an account with sufficient permissions to be able to access the database.

Apparently everyone agrees that Windows authentication is more secure than SQL Server authentication, for example here:

Secure way to connect: Windows Authentication or SQL Server Authentication and why?
Does the PCI standard forbid the use of Windows Authentication?
Any security related reasons to prefer Windows Authentication over Sql Server Authentication?

My question:
Is this still valid when the database was built with a developer's mindset, like described above?  (when the users needINSERT/UPDATE/DELETE permissions on the tables)

Why I'm asking this:
Our main app is an MS Access frontend with a SQL Server database. At the moment, we are using Windows authentication, but I'm considering switching to SQL authentication.

The problem I'm seeing:
Everyone connects to the database with his own Windows account.
So in order for the application to work, the Windows accounts of everyone and their dog need to have INSERT/UPDATE/DELETE permissions on the tables.

This means that literally EVERYBODY here is able to just create a new Access database, link a few tables from our main database and edit (or delete) them.
And yes, we have a few power users who really know how to do this.

From that viewpoint, it's hard for me to understand why this security threat is apparently not considered when recommending Windows authentication over SQL authentication.

To me, the possibility that anyone can just edit or delete tables with his Windows account screams insecurity.
On the other hand, the hypothetical danger that someone outside the IT department could get a clear-text SQL Server auth password from a config file is quite unlikely.
(given that there are no clear-text config files lying around on the users' machines - which is the case at our company)

As I said in the beginning - I know that this problem only exists because our app is written in a way so that the users need INSERT/UPDATE/DELETE permissions on the tables.
But then, there are a lot of databases like this out there...and I still see only recommendations that Windows auth is always more secure than SQL Server auth.
IMO, the answer should be (as always) "it depends", because you can't really tell without knowing more about the database in question.

So, does no one else see this problem or am I missing something?
Or is there a way to use Windows auth without all users being able to delete data?
(besides changing all data access to stored procs)


texts: Does mysql use deleted rows space in a table?

texts:
I am deleting a large data around 60GB from one of my innodb table which is around 70GB.

This is a rapidly growing table so i keep on inserting and deleting the records over a period of time. I cannot do DB optimization every time I delete.

So, does mysql use this deleted rows space (which is not given back to OS) for newly inserted rows?

update: innodb_file_per_table is ON already.


texts:
I want to identify in one column those who are present in the data but there is limit to only 10 nests. Is there easier or some other way for me to get the result that I want?

I hope you could help me.


texts: How is time stored in the FireFox sqlite database "places.sqlite"?

texts: How is time stored in the FireFox sqlite database "places.sqlite" ? Looking to modify the stored visit time on a few entries but I can't seem to figure out how to, is it encrypted?


texts: How to Find Consecutive count of a column based on a their status

texts:
I have a table containing various sites and billing information of those sites.
Ex:

 site_key |    billing_date     | Reason_type | rank
----------+---------------------+-------------+------
        3 | 2015-01-03 00:00:00 | RIGHT_BILL  |    1
        3 | 2015-02-03 00:00:00 | Zero_Bill   |    1
        3 | 2015-03-01 00:00:00 | Zero_Bill   |    2
        3 | 2015-04-03 00:00:00 | RIGHT_BILL  |    2
        3 | 2015-05-03 00:00:00 | RIGHT_BILL  |    3
        3 | 2015-06-03 00:00:00 | RIGHT_BILL  |    4
        3 | 2015-07-03 00:00:00 | RIGHT_BILL  |    5
        3 | 2015-08-03 00:00:00 | RIGHT_BILL  |    6
        3 | 2015-10-06 00:00:00 | RIGHT_BILL  |    7

What I need to find:
I need to find consecutive count for each site based on their Reason_Type and billing_date.
For example:If I receive any bill on 2015-10-06 whose Reason tyoe is RIGHT_BILL. So I need to find how many past consecutive cases were there for this scenario.
For example If I take billing_date =2015-03-01 its showing me rank as 2 which is right but again If i go and check for 2015-04-03 its showing 2 which is wrong it should also show as one since the last month billing_date was having Zero_Bill.

Expected output:

 site_key |    billing_date     | Reason_type | rank
----------+---------------------+-------------+------
        3 | 2015-01-03 00:00:00 | RIGHT_BILL  |    1
        3 | 2015-02-03 00:00:00 | Zero_Bill   |    1
        3 | 2015-03-01 00:00:00 | Zero_Bill   |    2
        3 | 2015-04-03 00:00:00 | RIGHT_BILL  |    1
        3 | 2015-05-03 00:00:00 | RIGHT_BILL  |    2
        3 | 2015-06-03 00:00:00 | RIGHT_BILL  |    3
        3 | 2015-07-03 00:00:00 | RIGHT_BILL  |    4
        3 | 2015-08-03 00:00:00 | RIGHT_BILL  |    5
        3 | 2015-10-06 00:00:00 | RIGHT_BILL  |    6

I tried:

Select site_key,
       billing_date,
       Reason_type,
       Dense_rank() over (partition by site_key,billing_date,reason_type order by billing_date) as rank
from indus_Dev_analytics.AVG_SCN
where site_key=3
order  by 1,2;


texts: Deadlock in REDO thread , 1 replica went OK other didn't?

texts:
I have a 3 node cluster and the 2 secondaries initially went into SUSPECT mode due to a deadlock.
 I tried to resume both and only one caught up while the other is continuously deadlocking.
I have to confess that troubleshooting deadlocks is not my main strength and with the little information provided by the xml I can't figure out what is going on.
This DB has 4 full text indexes and the pageID reported in the deadlock graph has the m_type 10 = IAM page ?
Is there any way I can get the REDO resumed or do I need to drop the replica and set it up again ?
I can't get my head around why did the other node catch up if the log sequence is the same ?

Thanks a lot

<deadlock>
  <victim-list>
    <victimProcess id="process23bb0c8" />
  </victim-list>
  <process-list>
    <process id="process23bb0c8" taskpriority="-20" logused="0" waitresource="PAGE: 21:1:47934 "
    XDES="0x407dc8ff0" lockMode="X" schedulerid="2" kpid="4604" status="background" spid="66" sbid="0" ecid="0" priority="0" trancount="0">
      <executionStack />
      <inputbuf />
    </process>
  </process-list>
  <resource-list>
    <pagelock fileid="1" pageid="47934" dbid="21" subresource="FULL"
    objectname="DB.sys.fulltext_index_1_69575286" id="lock417f18100" mode="IX" associatedObjectId="72057594044219392">
      <owner-list>
        <owner id="process23bb0c8" mode="IX" />
        <owner id="process23bb0c8" mode="IX" />
        <owner id="process23bb0c8" mode="IX" />
        <owner id="process23bb0c8" mode="IX" />
        <owner id="process23bb0c8" mode="X" requestType="wait" />
      </owner-list>
      <waiter-list>
        <waiter id="process23bb0c8" mode="X" requestType="wait" />
      </waiter-list>
    </pagelock>
  </resource-list>
</deadlock>


texts: viewing a column in mysql database using php script

texts:
I'm trying to print a row from a table in mysql using a PHP script - here is the code but its not working - it shows Bad query apparently there is no issue:

$conn = mysqli_connect($servername, $username, $password, $db);
if (!$conn) {
   die("Connection failed: " . mysqli_connect_error());
}

else{

echo "Connected successfully";

}

$any = "show columns from help_category";
$chk = mysqli_query($any) or die("Bad Query: $sql");
echo"
";
echo"
Type
";

while($row = mysqli_fetch_assoc($chk))

{

echo"
{$row['Type']}
";

}

echo"
";


texts: memc_delete called from a MySQL trigger loop fails randomly

texts:
I'm calling memc_delete from a MySQL trigger's code. The call is inside a loop. Logging the results, I see that memc_delete sometimes returns 1 (ok, memcached entry deleted) and sometimes returns 0 (entry is not deleted). I suspect the cause is too many calls in a row. If one call is raised before the previous one ends, it causes an error. Does it make any sense?

If yes, Is there any way to 'enqueue' calls, or set a 'wait until finish' mode?

Anyway, I'm trying to figure out the error's cause. I see here that the C/C++ memcached interface allows to translate the error code into a printable string with memcached_strerror, but an equivalent function callable from SQL doesn't seem to exist

Any ideas?

Thanks in advance


texts: What program should I use to open .db3 files?

texts:
I have a series of .db3 files in my computer. They are like filename1.db3, filename2.db3, filename3.db3, filename4.db3 and so on.

I can open filename1.db3 with sqlite editor. But when I open filename2.db3 file with sqlite editor, the sqlite editor gave me error like "It is not correct format".

What program should I use to open all of those .db3 files together? Thanks in advance.


texts:
I want to make colleges search system where a user enter any keywords in a search box and we can provide them some institutes with their details like institutes names, belonging hotspots, cities, and courses etc by searching in all tables (it could be area, city, institute name, courses or anything what we have).

But the problem is I have to make joins between all the tables and it is getting too slow. Can we optimize these tables to make the database more search/filter friendly?

For now I am using MongoDB to cache these data in single collection. As MongoDB isn't a relation database system, we have to compromise with many features.

Is MongoDB the only solution for me?
Or can these be optimized?

My Query is

SELECT i.name, GROUP_CONCAT(h.hotspot_name) hotspots,
       GROUP_CONCAT(ac.accreditation) accreditations, c.city_name from institutes i
left join areas a on i.area_id = a.area_id
left JOIN districts d on a.district_id = d.district_id
LEFT JOIN cities c on c.city_id = d.city_id
LEFT JOIN institute_hotspots ih on ih.inst_id = i.inst_id
LEFT JOIN hotspots h on ih.hotspot_id = h.hotspot_id
LEFT JOIN institute_accr ia on i.inst_id = ia.inst_id
LEFT JOIN accreditations ac on ia.accr_id = ac.accreditation_id
LEFT JOIN institute_courses ic ON i.inst_id = ic.inst_id
LEFT JOIN courses co on ic.course_id = co.id
LEFT JOIN course_names cn on co.course_id = cn.id
LEFT JOIN subcourses sc on co.subcourse_id = sc.id
LEFT JOIN course_types ct on co.type_id = ct.id
LEFT JOIN course_levels cl on co.level_id = cl.id
LEFT JOIN course_streams cs on co.stream_id = cs.id
LEFT JOIN course_category_relation ccr ON co.id = ccr.course_id
LEFT JOIN course_categories cc ON ccr.category_id = cc.id
WHERE i.name LIKE '%{QUERY}%'
OR i.name LIKE '%{QUERY}%'
OR  c.city_name LIKE '%{QUERY}%'
OR  a.area_name LIKE '%{QUERY}%'
OR  d.district_name LIKE '%{QUERY}%'
OR  h.hotspot_name LIKE '%{QUERY}%'
OR  ac.accreditation LIKE '%{QUERY}%'
OR  cn.name LIKE '%{QUERY}%'
OR  sc.name LIKE '%{QUERY}%'
OR  cl.name LIKE '%{QUERY}%'
OR  ct.name LIKE '%{QUERY}%'
OR  cs.name LIKE '%{QUERY}%'
OR  cc.name LIKE '%{QUERY}%'

GROUP BY i.inst_id
limit 10

It does not even run on my local system.

While preparing the diagram, I have changed some field names.

More detailed view


texts:
This should be a simple task. Can someone tell me why the below code is still inserting rows?

lock table wp_postmeta write;
insert into wp_postmeta values (null, 1,'test','test');

I've checked my DB user has full global priveleges


texts: How to use OR operator in CASE THEN statement

texts:
I have a situation like this

select col1, col2
from table1
where col1 = case when (booleanexp1) then a OR col1 = b
                  when (booleanexp1) then c OR col1 = d end

I mean I need to get where col1 = a or col1 = b when booleanexp1 is TRUE,
but I get a syntax error? How is the right way to perform something like this?


texts: Database design for school management to keep track of current and previous school years

texts:
What's the best way to design a students registration records and keep tracks of previous academic years?
I have a table to enroll students, something like this:
CREATE TABLE [dbo].[Students] (
    [StudentID]          INT             IDENTITY (1, 1) NOT NULL,
    [StudentNumber]      NVARCHAR (50)   NOT NULL,
    [EnrolmentDate]      DATE            NULL,
    [Class]              NVARCHAR (50)   NOT NULL,
    [StudentSurname]     NVARCHAR (60)   NOT NULL,
    [StudentFirstNames]  NVARCHAR (60)   NOT NULL,
    [FatherSurname]      NVARCHAR (100)  NULL,
    [FatherNames]        NVARCHAR (100)  NULL,
    [MotherSurname]      NVARCHAR (100)  NULL,
    [MotherNames]        NVARCHAR (100)  NULL,
    [SchoolYear]         NVARCHAR (50)   NOT NULL,
    [StudentPicture]     VARBINARY (MAX) NULL,
    CONSTRAINT [PK_Students] PRIMARY KEY CLUSTERED ([StudentID] ASC),
    CONSTRAINT [IX_Students] UNIQUE NONCLUSTERED ([StudentNumber] ASC),
    CONSTRAINT [FK_Students_AcademicYear] FOREIGN KEY ([SchoolYear]) REFERENCES [dbo].[AcademicYear] ([SchoolYear]) ON UPDATE CASCADE
);

I also have other tables for SchoolFees, MarksEntry, SchoolYear, etc.
So far everything works well for current year, I an just select where SchoolYear = current year, whether it's list of students, ,marks or fees paid/unpaid.
The challenge is to access data from previous years, I haven't figured out how to design the database or add a column to keep track of previous year.
When a student passes the exam, he/she promoted to a different class, so what I update in the Students table is: Class and SchoolYear, the rest of data remain the same like student number and other details.
So if I have a student in class 1B in school year 2020-2021 and promote to class 2B in year 2021-2022, the records of this student in class 1B in 2020-2021 are still in database but no longer accessible because that student no longer exist, their records have been modified.
What's the best and easy solution not to duplicate a student records but somewhat keep track of previous classes and school years when promoting a student to a different class?
How to promote the student to a different class?
Thanks


texts: whats best practice for Log Truncation in SQL Server?

texts:
I have a production DB in SQL server and wanted to put the final touches after the functionality is completed. prior to shipping it out i want to make sure i have some clean up in the SQL server DB and truncate and shrink log files?

can i have a nightly job run to truncate logs and shrink files?

this is what i have so far:
MY RECOVERY MODEL IS SIMPLE

ALTER proc [dbo].[UTIL_ShrinkDB_TruncateLog]
as

-- exec sp_helpfile
BACKUP LOG PMIS WITH TRUNCATE_ONLY

DBCC SHRINKFILE (PMIS, 1)

DBCC SHRINKFILE (PMIS, 1)


texts: Database architecture for big data store?

texts:
My team has developed service software with a PHP framework, so we are flexible to change database engine: MySQL, PostgreSQL etc. We have already more than 30 customers who wait to start using this service. My uncertainties are following:

Which DB engine is the best for use in such situations?
Is there also MySQL ok. or better to use PostgreSQL?
We use AWS so we plan to have own machine for DB hosting, is this the right decision?

More information:

Database will grow up to 10 GB in year.
Lots of selects but also inserts
Storing many texts and numbers. Files and images are stored on other storage.
Need 24/7 availability


texts: Worse performance after create empty table

texts:
I'm working with MySQL 5.7.10 and I have this issue.

I have this table to trace requests:

CREATE TABLE `invoice_requests` (
    `REQUEST_ID` VARCHAR(20) NOT NULL DEFAULT '' COLLATE 'utf8_spanish_ci',
    `INVOICE_ID` VARCHAR(50) NULL DEFAULT NULL COLLATE 'utf8_spanish_ci',
    `STARTTIME` DATETIME NULL DEFAULT NULL,
    `ENDTIME` DATETIME NULL DEFAULT NULL,
    `STATUS` VARCHAR(10) NOT NULL DEFAULT 'WORKING',
    PRIMARY KEY (`REQUEST_ID`),
    UNIQUE INDEX `UNQ_INVOICE_ID` (`INVOICE_ID`),
    INDEX `IDX_REQ_CODE_END_TIME` (`REQUEST_ID`, `ENDTIME`),
    INDEX `IDX_INV_NUMBER` (`INVOICE_ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

This table had 350MB and last year data.
So I did the following steps:

RENAME TABLE invoice_requests TO 201805_invoice_requests;
run the 'create table' above.

The first executions with this new, empty table has worsened performance of some queries (from 1 second to 15).

With MySQL EXPLAIN, we have checked that no index is used in the INNER JOIN with other tables, but the queries are the same and
the JOINs are made with the indexed field INVOICE_ID.

For testing purposes, we quit the invoice_requests JOIN in the query and the requested data returned fast again.

With this scenario, my questions would be:

Are those the right steps to backup a table (and their indexes too)?
Do I need the "old" index data in the new, empty table? Supposedly, no. But I don't understand this behaviour.

Any help would be very appreciated.


texts:
From msdn :

  Unlike a derived table, a CTE can be self-referencing and can be
  referenced multiple times in the same query.

I'm using CTEs quite a lot, but I've never thought deeply about the benefits of using them.

If I reference a CTE multiple times in the same query:

Is there any performance benefit?
If I'm doing a self join, will SQL Server scan the target tables twice?


texts: How do transactions help resilience to system failure?

texts:
I understand transactions were developed to meet two requirements:

Concurrent DB access
Resilience to system failures

I understand how the concurrency issue is motivated; to overcome the database consistency problem.

However, I have a question:  How do transactions help system resilience to failures?


texts: Split a very large SQL table to multiple smaller tables

texts: Is it possible to search a column within a table for a numerical value range of 1-2000 and then copy each full row into a different table? By doing so technically splitting the table into smaller parts.


texts: Exporting an Excel Spreadsheet containing a pivot table and charts to MySQL

texts:
I am looking at moving a large Excel spreadsheet to an SQL database  - most likely MySQL.

The excel spreadsheet contains 12 worksheets, with one of them being the Master, and the other 11 set up to gather information into separate comparitive tables and charts based on the data in the Master worksheet.

The master worksheet is a pivot table containing 90 columns and over 3000 rows. In the past, User's have tried to access this file at different times to edit it as it is on a network drive. Obviously, this not an ideal solution and I would like to set up this spreadsheet on a database.

From my brief research so far, I have seen that phpMyAdmin is a popular solution to export an excel worksheet, saved as a .csv file to a MySQL database. The solution would need to leave the data very accessible to a handful of users who have good excel skills but almost no programming knowledge.

I am wondering if the best solution is to move the entire spreadsheet to a database - ie. Convert the Master worksheet to a .csv file and export it using phpMyAdmin (Is it easy to set-up pivot tables again once this data is exported?), and then on MySQL, create the tables and charts that are currently on our other worksheets so that users can view and add/take from them using phpMyAdmin, or is there a better solution that would save me having to create the other 11 worksheets on MySQL? Or could the MySQL add-in for Excel be used in some way to generate a local copy of the data and other worksheets on everyone's local drive?

My knowledge of databases is elementary to say the least, so any help at all would be greatly appreciated.


texts: Restoring MySQL Master-Master Replication

texts:
I am trying to restore Master-Master MySQL 5.6 servers. The replication failed between DB1=>DB2 and so DB2 went out of sync.

GTIDs are enabled.

I have done an export and import from DB1 to DB2 and restarted the slave, all looks good, changes are being replicated.

I am unsure however to get DB1 slaved to DB2 again, with the slave running no changes are replicated. I am thinking I need to do reset master on one or both and restart the replication, does any one know the correct way to do this?


texts: Call to sysmail_delete_mailitems_sp keeps deadlocking

texts:
I run SQL Server 2012.
I have a maintenance job that runs every night. At the end of the maintenance, I do some cleanup and call this:

Begin try
Exec msdb.dbo.sysmail_delete_log_sp @logged_before = '20160613';
EXECUTE msdb.dbo.sysmail_delete_mailitems_sp  @sent_before = '20160613';
End try
Begin catch
-- Do some log error
End catch

I put the transaction in a Try/Catch but it doesn't seem to be working. I keep getting this error :
"Error 1205, Severity 13, level 18 : Transaction (Process ID 112) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction."

If I run the transaction manually, it runs fast and I don't get any errors.
Any ideas why I keep getting this error or how I can catch it so it doesn't fail my job?


texts: Problem reading data from secondary when reorganizing clustered index

texts:
We have an AOAG in SQL Server 2014 SP2 CU5 (3 nodes). There is a database with Read Committed Snapshot Isolation level ON. We have a large table compressed. Some of our bigger queries on this table are performed into the secondary.
Then there is a night job on the primary node to reorganize indexes on several tables. When it hits the clustered index of the mentioned table we get the following error:

Transaction aborted when accessing versioned row in table 'xxxx' in database 'yyyy'. Requested versioned row was not found because the readable secondary access is not allowed for the operation that attempted to create the version.

At some point, the big queries were performing the reads with the hint READUNCOMMITTED. I thought that it was the cause of this error so I removed them. But the error is still there.
Any ideas?
Current setup:

02 secondary is on synchronous mode
03 secondary on asynchronous mode

Table details

RowCounts: 122.567.668
TotalSpaceMB: 18.460
UsedSpaceMB: 18.238

Definitions:
CREATE TABLE [dbo].[big_table](
[ID] [int] NOT NULL IDENTITY(1, 1),
1 [int] NULL,
2 [datetime] NULL,
3 [int] NULL,
4 [int] NULL CONSTRAINT [DF_ccc_bUnits] DEFAULT ((0)),
5 [money] NULL,
6 [money] NULL,
7 [int] NULL,
8 [int] NULL CONSTRAINT [DF_ccc_MinDays] DEFAULT ((0)),
9 [int] NULL,
10 [int] NULL,
11 [float] NULL,
12 [money] NULL,
13 [int] NULL,
14 [int] NULL,
15 [nvarchar] (200) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
16 [money] NULL,
17 [money] NULL,
18 [int] NULL,
19 [int] NULL,
20 [money] NULL,
21 [money] NULL,
22 [money] NULL,
23 [money] NULL,
24 [money] NULL,
25 [datetime] NOT NULL CONSTRAINT [DFcccadded] DEFAULT (getdate()),
26 [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
27 [money] NOT NULL CONSTRAINT [DFcccBrf] DEFAULT ((0)),
29 [money] NOT NULL CONSTRAINT [DFcccHB] DEFAULT ((0)),
30 [money] NOT NULL CONSTRAINT [DFcccFB] DEFAULT ((0)),
31 [money] NOT NULL CONSTRAINT [DFcccAllBoards] DEFAULT ((0)),
32 [money] NOT NULL CONSTRAINT [DFcccChildBrf] DEFAULT ((0)),
33 [money] NOT NULL CONSTRAINT [DFcccChildHB] DEFAULT ((0)),
34 [money] NOT NULL CONSTRAINT [DFcccChildFB] DEFAULT ((0)),
35 [money] NOT NULL CONSTRAINT [DFcccChildAllBoards] DEFAULT ((0)),
36 [int] NULL CONSTRAINT [DFcccShow_1] DEFAULT ((0)),
37 [timestamp] NOT NULL,
38 [money] NULL,
39 [money] NULL,
40 [money] NULL,
41 [money] NULL,
42 [money] NULL,
43 [money] NULL,
44 [money] NULL,
45 [money] NULL,
46 [int] NOT NULL CONSTRAINT [DFcccReleaseHour] DEFAULT ((0)),
47 [int] NULL,
48 [int] NULL,
49 [money] NULL,
50 [money] NULL,
51 [float] NULL
) ON [PRIMARY]
WITH (DATA_COMPRESSION = PAGE)
GO
CREATE UNIQUE CLUSTERED INDEX [IXccc] ON [dbo].[big_table] (1, 2) WITH (FILLFACTOR=90, DATA_COMPRESSION = PAGE) ON [PRIMARY]
GO
ALTER TABLE [dbo].[big_table] ADD CONSTRAINT [PKccc] PRIMARY KEY NONCLUSTERED ([ID]) WITH (DATA_COMPRESSION = PAGE) ON [secondary]
GO
CREATE UNIQUE NONCLUSTERED INDEX [IXcccstamp] ON [dbo].[big_table] (36) INCLUDE (1, 2) WITH (FILLFACTOR=100) ON [PRIMARY]
GO


texts: SSH Tunnel option of PgAdmin4 not working

texts:
I am using PgAdmin4 3.3, and I am trying to setup a server connection to a database through an SSH tunnel.

My database runs on port 30000 on the host. Setting it to 5432 is unfortunately not possible.

When I tunnel that port manually in the terminal it works perfectly: ssh -L 30000:127.0.0.1:30000 user@host

My server configuration is then to connect to localhost on port 30000.

If I now disconnect the ssh session and try to configure it directly in the pgAdmin UI, it wont work.

After I save the config and try to connect to the database it takes about a minute and then I get a connection lost message.

Thanks in advance!


texts: Oldest unsent transaction alert for Availability Group

texts:
In SQL Server 2008 when using database mirroring, it was possible to set up alerts when the oldest unsent transaction exceeded a certain threshold.  We could check the state of any database mirroring using the Launch Database Mirroring Monitor menu command.

In SQL Server 2012 we now have Availability Groups.  It would seem to me that the same potential problem exists that if transactions cannot be sent to the secondary server then the mirroring will be suspended, and potentially the transaction logs will fill up all our disk space.  However, the Database Mirroring Monitor tool does not seem to recognise databases in Availability Groups as being mirrored.

Is there a way to set up an alert if the oldest unsent transaction exceeds a certain value?  Is there a way to set that certain value?


texts: How to make a column mandatory in every Insert / Update (not meaning default null)

texts:
Following table:

CREATE TABLE kontrolle.negativtext
(
  id serial NOT NULL,
  ausschluss character varying(255) NOT NULL,
  nur_domains character varying(255)[] NOT NULL DEFAULT '{}'::character varying[],
  nicht_domains character varying(255)[] NOT NULL DEFAULT '{}'::character varying[],
  bemerkung character varying(255),
  last_change_user character varying(3) NOT NULL,
  last_change_timestamp timestamp without time zone,
  hits integer NOT NULL DEFAULT 0,
  is_regex boolean NOT NULL DEFAULT false,
  check_doc negative_doc_check[] DEFAULT '{CONTENT_TXT_DIRTY, CONTENT_TXT_CLEAN}',
  CONSTRAINT negativtext_pkey PRIMARY KEY (id)
);

Values of datarecords in this table should be changeable by multiple users. I want to force everyone updating or inserting values in this table to provide a username (we don't need to discuss why this is no foreign key).
Setting the column to NOT NULL DEFAULT NULL will prevent the value to ever be null, which is fine. But when user abc inserted a row and user xyz updates it later, xyz should need to provide a value for column last_change_user, meaning this update statement should throw an error:

Update kontrolle.negativtext set ausschluss = :a, regex : rgx where id = :id

even if there is already a username set, but this should work:

Update kontrolle.negativtext
set ausschluss = :a, regex : rgx, last_change_user = :user
where id = :id

I think a constraint wouldn't work, a trigger before update on should work but I don't have success:

create trigger mandatory_username before update on kontrolle.negativtext
for each row
execute procedure check_mandatory_username();

create or replace function check_mandatory_username()
returns trigger as $bla$
begin
    if NEW.last_change_user is null then
        raise exception 'no last_change_user provided'
        USING HINT = 'please add your name';
    else return NEW;
    end if;
end;
$bla$ language plpgsql;

This never raises an exception. If I remove the condition-parts:

create or replace function check_mandatory_username()
returns trigger as $bla$
begin
    raise exception 'no last_change_user provided'
    USING HINT = 'please add your name';
end;
$bla$ language plpgsql;

then I get the message every time, so in general this could be the way to go. But I think NEW.last_change_user does not check the provided name, but only the new value (which is the old name).

Somebody have an idea?


texts: What privileges do I need to change the password of sys in an Oracle Database?

texts: Our identity management tool wants to change the sys password regularly. It fails with the ORA-01031. What privileges or roles do I need to grant the identity management tool user to be able to change the sys password?


texts: MySql GROUP BY, ORDER BY LIMIT query taking more than 5000 seconds


texts:
imagine you have follwing table given

id
value

1
5

2
3

3
7

4
1

Depends on your case you need the ID of the next smaller
or greater value.
So the select of ID=2 would give u either 4 or 1.
How would you solve that?
thanks in advice.
Eing


texts:
I have huge outputs from pg_stat_activity and the result from the second "combination of blocked and blocking activity" report from https://wiki.postgresql.org/wiki/Lock_Monitoring (2nd version), with a very surprising conclusion: it looks like SELECTs are blocking SELECTs.

For example, the second report shows that this query:

SELECT infovalue from "info-field" where infotype ='xxxx'
                AND clientidnum = 146087240

is blocked by this query:

SELECT infotype FROM "info-field"
                    WHERE infotype = 'yyyy'
                    and refidnum = 0 LIMIT 1

And, even more surprising, that two instances of a single query are blocking each other:

SELECT "info-field".* FROM "info-field" where
    "info-field".infotype = 'www'
     and "info-field".infowho = 210745140 order by infoid desc LIMIT 1

and

SELECT "info-field".* FROM "info-field" where
    "info-field".infotype = 'www'
     and "info-field".infowho = 229912040 order by infoid desc LIMIT 1

The high-level problem I'm looking into is that there are occasional bursts of activity where there are dozens of postgres processes spinning in sys time (so my guess is lock spinning, hence the investigation into locks).

My first issue is that I'm surprised by this apparent read-blocking-read problem. This is PostgreSQL 9.3.5. What could cause something like this?

The second is that the number of active processes from pg_stat_activity is ~~60, while the number of rows in the second report is ~~1900! How could such difference happen? There are no INSERT or UPDATE queries going on with the tables involved in SELECTs, and there are ~~5 INSERT or UPDATES queries running total in the reports.

Table details (on the related columns):

infotype text,
infowho integer,
clientidnum integer,
refidnum integer,
infoid integer serial primary key

Indexes:

(refidnum, infotype),
(clientidnum, infotype),
(infowho, infotype),
(infoid),

partial index on
  (refidnum, xfield, infotype)
where
  xfield in (1,2,3)


texts: ST_GeomFromGeoJSON() Causes postgres to crash

texts:
I'm running into a strange problem which I can't solve. The server crashes when executing some postgis related queries.
After some debugging, using the examples provided by postgis, it appears the ST_GEOMFROMGeoJSON() function causes to server to crash.

Crashes:

SELECT ST_AsText(ST_GeomFromGeoJSON('{"type":"Point","coordinates":[-48.23456,20.12345]}')) As wkt;

Operates normal:

SELECT ST_AsText(
         ST_Transform(
               ST_GeomFromText('POLYGON((743238 2967416,743238 2967450,
                                         743265 2967450,743265.625 2967416,743238 2967416))',2249)
         ,4326)
        ) As wgs_geom;

When looking into the log I find these entries related to the crash:

2014-11-21 11:27:46 CET LOG:  server process (PID 2377) was terminated by signal 11: Segmentation fault
2014-11-21 11:27:46 CET DETAIL:  Failed process was running: SELECT ST_AsText(ST_GeomFromGeoJSON('{"type":"Point","coordinates":[-48.23456,20.12345]}')) As wkt;
2014-11-21 11:27:46 CET LOG:  terminating any other active server processes
2014-11-21 11:27:46 CET WARNING:  terminating connection because of crash of another server process
2014-11-21 11:27:46 CET DETAIL:  The postmaster has commanded this server process to roll back the current transaction and exit, because another server process exited abnormally and possibly corrupted shared memory.
2014-11-21 11:27:46 CET HINT:  In a moment you should be able to reconnect to the database and repeat your command.
2014-11-21 11:27:46 CET LOG:  all server processes terminated; reinitializing
2014-11-21 11:27:46 CET LOG:  database system was interrupted; last known up at 2014-11-21 11:24:11 CET
2014-11-21 11:27:46 CET LOG:  database system was not properly shut down; automatic recovery in progress
2014-11-21 11:27:46 CET LOG:  record with zero length at 18/34BCAD4
2014-11-21 11:27:46 CET LOG:  redo is not required
2014-11-21 11:27:46 CET LOG:  database system is ready to accept connections
2014-11-21 11:27:46 CET LOG:  autovacuum launcher started

Searching the web did not yield into a solution. Maybe I´m not searching for the right problem?

I tried to restart the server as maybe the filesystem has some faults, without results.
The server itself is a Ubuntu 12.04 machine as a VPS.
The postgresql server is 9.3.5 and Postgis 2.1.4

Update
As Craig Ringer suggested I used gdb to see what happens when the process crashes. This is the first output:

Program received signal SIGSEGV, Segmentation fault.
0xb70dda59 in _IO_vfprintf_internal (s=0xbfde7150, format=<optimized out>,  ap=0xbfde7298 "") at vfprintf.c:1630
1630    vfprintf.c: No such file or directory.

Also this is the output ofpostgis_full_verion():

POSTGIS="2.1.4 r12966" GEOS="3.3.3-CAPI-1.7.4" PROJ="Rel. 4.7.1, 23 September 2009"
GDAL="GDAL 1.9.0, released 2011/12/29" LIBXML="2.7.8"
LIBJSON="UNKNOWN" TOPOLOGY RASTER

Am I right to say something is wrong with vfprintf.c? eg it does not exists?

It says LIBJSON="UNKNOWN" could that be of influence?


texts: Number of logical reads in SQL server JOIN

texts:
Please refer to the SELECT query shown below, which includes a JOIN between two tables that I have renamed as Table1 and Table2, where the latter is a temporary one:

Table1 has over 15M records, whereas Table2 is around 9K.
Product team says the JOIN acts as a filter as far as reads are concerned and that they should be 9K at most. However, DBA insists that the JOIN here is not a filter and that the reads are more than 14M because there is no WHERE clause here.

My humble opinion (not being a SQL developer or DBA myself) is that the JOIN can be thought of as a filter in the final resultset, but SQL Server somehow has to read the entire Table1 before it can actually perform the JOIN with Table2.
The reason that prompted this discussion was a performance issue and the fact that the execution plan shows +14M of actual rows associated with this query:

Unfortunately, this is proprietary code I am not allowed to post publicly. My question is more geared towards what takes place during a SQL JOIN under the hood. Does SQL Server need to read the entire tables before returning the result set?
The exact question would be: Does it make any sense that SQL Server had to make +14M reads to perform the JOIN in this scenario?


texts: What changes to Oracle installation are needed to work with Microsoft MSDTC

texts:
We have windows 2008 and 2003 servers that use COM PLUS-MSDTC and distributed transactions with 11g(11.2.0.3) running on SunOS and SPARC hardware.  The oracle server was installed years ago by a DBA that is long gone.

We have built a new oracle server same version.  Problem is that COM PLUS-MSDTC and distributed transactions do not work with the new server and our current DBA has never installed a server that is supposed to work with COM PLUS-MSDTC and distributed transactions.

If we point our windows server to the existing oracle server no problem, we point it to new one and our COM PLUS apps just hang.

Is there something that needs to be done to the Oracle installation to allow it to work with COM PLUS-MSDTC and distributed transactions?  Some script or setting?


texts: What is the advantage of pluggable database over normal databases?

texts: Why creating pluggable database over normal database is good for system performance.


texts: GRANT ALL ON ALL TABLES IN SCHEMA does not allow user to see tables

texts:
I have a pretty simple script to create a new database and to create a new user to access this database. This is run by the default postgres user. After granting access to the database and schema to the user, the newly created user is still not able to see the tables using \dt in psql.  Here are some snippets of the script to show what I've tried so far:

CREATE USER api WITH ENCRYPTED PASSWORD 'password';

ALTER DEFAULT PRIVILEGES
    FOR USER api
    IN SCHEMA public
    GRANT ALL ON ALL TABLES TO api;

DROP DATABASE IF EXISTS new_db;
CREATE DATABASE new_db;

CREATE TABLE addresses (
    address_id INTEGER,
    address_line_1 VARCHAR(50) NOT NULL,
    address_line_2 VARCHAR(50),
    city VARCHAR(50) NOT NULL,
    state VARCHAR(2) NOT NULL,
    zipcode VARCHAR(12) NOT NULL,
    PRIMARY KEY (address_id)
);

-- Create more tables....

-- Added these in for good measure at the end:
GRANT all PRIVILEGES on DATABASE new_db to api;
GRANT ALL ON DATABASE new_db TO api;
GRANT ALL ON SCHEMA public to api;
GRANT ALL ON ALL TABLES IN SCHEMA public TO api;

After running this, \dt shows all the tables in psql when logged in as the postgres user.  \dt shows Did not find any relations. when logged in as the api user.

Running \dn+ with the postgres user gives me this information:

  Name  |  Owner   |   Access privileges   |      Description
--------+----------+-----------------------+------------------------
 public | postgres | postgres=UC/postgres +| standard public schema
        |          | =UC/postgres         +|
        |          | api=UC/postgres       |

Version information:
PostgreSQL 10.5 (Ubuntu 10.5-2.pgdg16.04+1) on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 5.4.0-6ubuntu1~16.04.10) 5.4.0 20160609, 64-bit

Other SO questions I've encountered so far:

Granting access to all tables for a user
https://stackoverflow.com/questions/7866572/why-can-i-only-see-postgresql-relations-or-tables-when-im-logged-in-as-the-post
https://stackoverflow.com/questions/7758533/postgresql-database-owner-cant-access-database-no-relations-found
https://stackoverflow.com/questions/37543078/cant-see-tables-in-postgresql

I've looked at countless SO questions and tried everything suggested, yet still no luck. Is there anything else I can check or try? My hunch is that I'm missing something obvious.


texts: SQL Server 2012 Managment Studio not saving auto recovery files

texts:
I tried to go to Tools > Environment > AutoRecover.

The check box of save autorecover is checked.
Save every "5" minutes.
Keep autorecover info for: 7 days.

But it doesn't save any auto recover files (I searched inside my user folder for *.sql), and doesn't suggest to auto recover when the Management Studio closes by a power down.

I searched the registry to see if something was changed manually in HKEY_CURRENT_USER\Software\Microsoft\Microsoft SQL Server but only found one key: CustomerFeed.


texts: MySQL Cluster setup, receiving error "Specify node id"

texts:
I am attempting to set up a MySQL Cluster.
I have the following setup:

One management node
Two data nodes
One SQL node

Below is the config.ini file:
[ndbd default]
# Options affecting ndbd processes on all data nodes:
NoOfReplicas=1     # Number of replicas

DataMemory=256M    # How much memory to allocate for data storage

IndexMemory=128M   # How much memory to allocate for index storage

#Directory for Data Node
DataDir=/var/lib/mysql-cluster

[ndb_mgmd]

# Management process options:
HostName=x.x.x.21

datadir=/var/lib/mysql-cluster

# Storage Nodes
[NDBD]

HostName=x.x.x.188
DataDir= /var/lib/mysql-cluster

[NDBD]

HostName=x.x.x.189
DataDir= /var/lib/mysql-cluster

# Setup node IDs for MySQL API-servers (clients of the cluster)
[mysqld]
# SQL node options:
hostname=x.x.x.22

Below is my.cnf file
[mysqld]

ndbcluster # run NDB storage engine

ndb-connectstring=X.X.X.21 # location of management server

[mysql_cluster]

ndb-connectstring=X.X.X.21 # location of management server

But when I start command ndb_mgmd -f /etc/config.ini --initial
I am getting the following error:

MySQL Cluster Management Server mysql-5.6.28 ndb-7.4.10
2018-05-09 14:29:08 [MgmtSrvr] ERROR    -- Could not determine which nodeid to use for this node. Specify it with --ndb-nodeid= on command line

Can anyone please help?


texts:
I have a column in my database called Horse_Name. This currently includes the country if outside of UK in brackets (ire),(fra) ect.

Is it possible to run a query to remove the brackets and the content very much like in excel whereby you can replace all (*) within the given column?

An example would be as follows

Current            Outcome
----------------+--------------
Horse_Name      |  Horse_Name
----------------+--------------
Rocket(FRA)     |   Rocket
Apple Jade(IRE) |   Apple Jade
Zac             |   Zac

I would like to go through the whole column named Horse_Name to remove the brackets and data.

I have tried the following code but it only finds the values and does not replace them

SELECT REPLACE(Horse_Name,
               SUBSTRING(Horse_Name, LOCATE('(', Horse_Name), LENGTH(Horse_Name) - LOCATE(')',
               REVERSE(Horse_Name)) - LOCATE('(', Horse_Name) + 2), '') AS Horse_Name
FROM  TFResults;

Kind Regards.


texts:
I have a working MariaDB 10.2 running on my CentOS 6 server. I can connect to the DB from another machine on the LAN.

Out of curiosity, I wanted to revisit how this was made possible. Everything I read mentions using bind-address in the my.cnf file, but when I examine this file, there is no such directive. Neither is there anything in the files /etc/my.cnf.d/*.cnf.

Is network access a default in this case, or is there some other mysterious directive?