texts: mysql/aurora not immediately seeing results of committed transactions

texts:
In a system running against an AWS aurora-mysql database, the following (obviously extremely simplified) code runs. It's a highly concurrent system, multiple threads, multiple connections to db. The problem we're running into is when two threads are both trying to insert 'abc' into the table at almost the same time.

As expected, if thread1 & thread2 start at the same time, thread2 gets stuck waiting on the "select...for update" statement at the start. Once thread1 commits its transaction, the lock is released and thread2 continues executing.

The problem is that thread2's next line selecting from tbl_foo returns no result causing it to execute the wrong block of code after, despite it just having been inserted. In fact, the later insert by thread2 will fail due to a unique constraint violation. Is this a known problem with mysql and/or aurora read caching? Is there some configuration setting that will change it?

<start transaction>

SELECT * FROM tbl_user WHERE userID = 123 FOR UPDATE;

SELECT * FROM tbl_foo WHERE fk_user = 123 AND unique_column = 'abc';

if (found row in tbl_foo)
    do stuff
else
    do different stuff including inserting (123, 'abc') into tbl_foo

<commit transaction>


texts: Mongo WriteConflictException in log file

texts:
I am experiencing ALOT of this WriteConflictException in my mongo log file, this does not seem to affect the application, because I am not seeing any special errors and everything seems to work fine.

Should I be concerned about this?

> <Time> D WRITE    [conn1397953] Caught
> WriteConflictException doing plan execution on ***, attempt: 1
> retrying 2018-11-20T06:35:39.612+0000 D WRITE    [conn1397956] Caught
> WriteConflictException doing plan execution on ***, attempt: 1
> retrying 2018-11-20T06:35:40.149+0000 D WRITE    [conn961669] Caught
> WriteConflictException doing plan execution on ***,
> attempt: 1 retrying 2018-11-20T06:35:40.149+0000 D WRITE
> [conn961669] Caught WriteConflictException doing findAndModify on
> ***, attempt: 0 retrying

Thanks


texts: Naming a datafile with spaces in the name

texts:
I would like to know if there could be a problem when naming a datafile with spaces in the name, I have an Oracle 11gR2 database, using ASM as storage, a datafile was created, belonging to an application's tablespace, with a name that contains spaces:
"+DATA/oracl/data/my new dtf"
that is, the name of the tablespace is: my new dtf, it was created without problems, but I have doubts that it could cause a conflict.


texts: € Euro sign/symbol not showing up on Oracle 10gr2 and aix 6.1

texts:
this is Oracle 10gr2 on aix 6100-02

The issue is that I do not manage to see the € sign from Oracle.

This is an easy way to see the issue:

SQL> insert into prueba values ('€');
SQL> commit;
SQL> select * from prueba;
EURO
------------------------------------------------------------
¿
SQL>

This are the tests I have already done.

I see it from the shell:

oracle@krats:/u01#perl -Mcharnames=:full -CS -wle 'print "\N{EURO SIGN}"'
€
oracle@krats:/u01#

But I do not see it from the sql  :

SQL> select chr(128) from dual;

C
-

SQL>

This database has the following NLS_SESSION_PARAMETERS;

SQL> SELECT * FROM NLS_SESSION_PARAMETERS;
PARAMETER                      VALUE
------------------------------ ----------------------------------------
NLS_LANGUAGE                   SPANISH
NLS_TERRITORY                  SPAIN
NLS_CURRENCY                   ¿
NLS_ISO_CURRENCY               SPAIN
NLS_NUMERIC_CHARACTERS         ,.
NLS_CALENDAR                   GREGORIAN
NLS_DATE_FORMAT                DD/MM/RR
NLS_DATE_LANGUAGE              SPANISH
NLS_SORT                       SPANISH
NLS_TIME_FORMAT                HH24:MI:SSXFF
NLS_TIMESTAMP_FORMAT           DD/MM/RR HH24:MI:SSXFF

PARAMETER                      VALUE
------------------------------ ----------------------------------------
NLS_TIME_TZ_FORMAT             HH24:MI:SSXFF TZR
NLS_TIMESTAMP_TZ_FORMAT        DD/MM/RR HH24:MI:SSXFF TZR
NLS_DUAL_CURRENCY              ¿
NLS_COMP                       BINARY
NLS_LENGTH_SEMANTICS           BYTE
NLS_NCHAR_CONV_EXCP            FALSE

17 filas seleccionadas.

SQL>

I have create a table just to show this up.

oracle@krats:/u01#NLS_LANG=SPANISH_SPAIN.UTF8
oracle@krats:/u01#export NLS_LANG
oracle@krats:/u01#sqlplus "/ as sysdba"
SQL> create table prueba (euro nvarchar2(20));
table created.
SQL> insert into prueba values ('€');
1 fila creada.
SQL> commit;
Confirmación terminada.
SQL> select * from prueba;
EURO
------------------------------------------------------------
¿
SQL>

Finally, I'm not sure if important but this are the oracle user env vars:

SQL> host env;
_=/usr/bin/env
LANG=en_US
LOGIN=oracle
PAGER=/usr/bin/pg
PATH=/usr/bin:/etc:/usr/sbin:/usr/ucb:/u01/bin:/usr/bin/X11:/sbin:.:/u01/oracle/product/10.2/db_1/bin/
NLS_LANG=SPANISH_SPAIN.WE8ISO8859P1
ORACLE_BASE=/u01/oracle/product
LC__FASTMSG=true
EDITOR=/usr/bin/vim
KM_CRYPTO_LIB_PATH=/usr/lib/libkmcryptolib.so
LOGNAME=oracle
MAIL=/usr/spool/mail/oracle
LOCPATH=/usr/lib/nls/loc
ORACLE_SID=DIESEL
PS1=oracle@krats:${PWD}#
USER=oracle
AUTHSTATE=files
ORA_NLS10=/u01/oracle/product/10.2/db_1/nls/data
SHELL=/usr/bin/ksh
ODMDIR=/etc/objrepos
R_SHLIB_LD_LIBRARY_PATH=/usr/lib
HOME=/u01
FCEDIT=/usr/bin/vi
TERM=vt100
MAILMSG=[YOU HAVE NEW MAIL]
ORACLE_HOME=/u01/oracle/product/10.2/db_1
PWD=/u01
TZ=Europe/Madrid
KM_SUPPORT_LIB_PATH=/usr/lib/libkmsvcshlib.so
ORACLE_BIN=/u01/oracle/product/10.2/db_1/bin/
A__z=! LOGNAME
NLSPATH=/usr/lib/nls/msg/%L/%N:/usr/lib/nls/msg/%L/%N.cat

SQL>

Has anyone faced a similar situation?

Many thanks.


texts: Dba.getCluster: This function is not available through a session to a standalone instance (RuntimeError)

texts:
I am using mysql innodb cluster. Connect to my instance use \c root@localhost:3306.
The error message pops out when I issue var cluster = dba.getCluster('testCluster').

I've already created the cluster in last session. But when I reconnect it does not work anymore.

The only thing I changed is hostname from localhost to db005 and restart mysql server. Don't even know if it has something to do with my problem. Anybody has idea what happened?

Did some searches on google. Seems this is the only resource from mysql forum. But still not able to solve my problem.
https://forums.mysql.com/read.php?177,653826,657465#msg-657465

Thx!


texts:
I have problems with my dynamic filter in SQL Server.

My problem is that the filters aren't working. When I try to execute the stored procedure, I just get all the data without filtering.

I'm new to this.

alter PROCEDURE Member_WS
    @Program int ='',
    @Card nvarchar(30)= '',
    @Account nvarchar(128) ='',
    @Member bigint =0,
    @Email nvarchar(256)='',
    @Phone nvarchar(20)='',
    @ResponseType nvarchar ='',
    @FirstName nvarchar =''
AS
    DECLARE @sql varchar(max),
            @filtro varchar(500)

    set @filtro = ''

    if @Program = '' and @Card = '' and @Account = '' and @Member = '' and @Email = '' and @Phone = '' and @FirstName = ''
    set @filtro = ''
    else
    begin
        set @filtro = ''
        if @Program <> ''
            set @filtro = @filtro + ' and' set @filtro =@filtro + '[Program].ProgramID =  convert(varchar,@Program)'
        if @FirstName <> ''
             Select @filtro = @filtro + ' and [member].FirstName= ' + @FirstName
        if @Card <> ''
             Select @filtro = @filtro + ' and [account].IIN + REPLICATE(0, (8 - len (LS001..[account].AccountNumber))) + ltrim(str(LS001..[account].AccountNumber)) +  ltrim(str(LS001..[account].verificationDigitSufix))= ' +  @Card
        if @Account <> ''
             Select @filtro = @filtro + ' and [Account].AccountID = ' + @Account
        if @Member <> ''
             Select @filtro = @filtro + ' and [member].MemberID= ' +  convert(varchar,@Member)
        if @Email <> ''
             Select @filtro = @filtro + ' and [user].email= ' + char(39) + @email + char(39)
        if @Phone <> ''
             Select @filtro = @filtro + ' and [user].PhoneNumber= ' + @Phone
    end
--------------------------------------------
Exec Member_WS @Program = 50
GO

This is not the full query just the filter part.


texts:
What is the advantage of partition Date field into individual Year(int), Month(int), Day(int), Time(time) columns?

My manager said this is well known practice and query performance is good. Is this true?

Thanks in advance

Edit: and this columns are put under a clustered index.

Edit: This is not a accounting database. There is no need for thirteen month or 30 Feb.

Edit: table script

/****** Object:  Table [dbo].[tbl_PlayerActionLog]    Script Date: 14/8/2014 9:47:28 AM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[tbl_PlayerActionLog](
    [LogId] [bigint] IDENTITY(1,1) NOT NULL,
    [LogYear] [int] NOT NULL CONSTRAINT [DF_tbl_PlayerActionLog_LogYear]  DEFAULT (datepart(year,sysdatetime())),
    [LogMonth] [int] NOT NULL CONSTRAINT [DF_tbl_PlayerActionLog_LogMonth]  DEFAULT (datepart(month,sysdatetime())),
    [LogDay] [int] NOT NULL CONSTRAINT [DF_tbl_PlayerActionLog_LogDay]  DEFAULT (datepart(day,sysdatetime())),
    [LogTime] [time](7) NULL CONSTRAINT [DF_tbl_PlayerActionLog_LogTime]  DEFAULT (sysdatetime()),
    [PlayerActionTypeId] [int] NOT NULL,
    [MerchantId] [int] NOT NULL,
    [UserName] [varchar](50) NOT NULL,
    [BetModeId] [int] NULL,
    [OSVersion] [varchar](20) NULL,
    [SessionId] [varchar](30) NULL,
    [IpAddress] [varchar](15) NOT NULL,
    [LogDetail] [nvarchar](max) NOT NULL,
 CONSTRAINT [PK_tbl_PlayerActionLog] PRIMARY KEY NONCLUSTERED
(
    [LogId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

ALTER TABLE [dbo].[tbl_PlayerActionLog]  WITH CHECK ADD  CONSTRAINT [FK_tbl_PlayerActionLog_tbl_LookupPlayerActionType] FOREIGN KEY([PlayerActionTypeId])
REFERENCES [dbo].[tbl_LookupPlayerActionType] ([PlayerActionTypeId])
GO

ALTER TABLE [dbo].[tbl_PlayerActionLog] CHECK CONSTRAINT [FK_tbl_PlayerActionLog_tbl_LookupPlayerActionType]
GO
/****** Object:  Index [tbl_PlayerActionLog_idx]    Script Date: 14/8/2014 10:00:23 AM ******/
CREATE CLUSTERED INDEX [tbl_PlayerActionLog_idx] ON [dbo].[tbl_PlayerActionLog]
(
    [LogYear] ASC,
    [LogMonth] ASC,
    [LogDay] ASC,
    [LogTime] ASC,
    [MerchantId] ASC,
    [PlayerActionTypeId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
GO


texts: SSMS Timeout when browsing for sql servers?

texts:
When connecting to a sql server instance one has the option of browsing the network for sql instances.  This used to work for (returning ~50 entries) us until we were migrated into a larger domain with a couple thousand machines, with the possibility of a few thousand sql instances.  It now searches for 2-3 minutes then the list is empty.

Does anyone know if there is a timeout on how long it will search?  If so, is the timeout configurable?

(I know 2-3k servers isn't useful to scroll through - but still need an answer)


texts: How do I generate a SQL script from my diagram in MySQL Workbench?

texts: I've created an EER diagram with tables, foreign keys, etc in MySQL Workbench and now I want to generate a ".sql" script that will create this database. How do I do that?


texts: Merge multiple rows in table setting one column based on value in rows

texts:
I have some data that looks something like this:

key B
1   true
1   false
2   false
2   false

And I want to roll it up so that if column B has two false values then the resulting value for B is false; otherwise, if there is a true and false value the value for B is true. Like this:

key B
1   true
2   false

Is there a way that this can be done with (Snowflake) SQL? I first thought of a pivot table but this doesn't quite seem to be what pivots do.


texts: What is the most effective way to cluster MySQL for my requirements?

texts:
Basically I have 3 edge servers and 3 application servers. The edge servers are running nginx and load balance between the application servers. The application servers are running Virtualmin GPL with PHP applications deployed. All are running CentOS 5.7

At the moment MySQL is running on one of the edge servers, and is the single point of failure - I'm looking to cluster it between the application servers, but I'm just not sure what the best thing would be to do. I was thinking of running MySQL load balancers on 2 of the edge servers, then the MySQL instances on all 3 of the application servers and the MySQL cluster management server on one of the edge servers.

Does anyone have any other ideas? The problem with the aforementioned setup is the apps will be connecting to the edge servers for MySQL which could affect performance.

As I'm the developer of most of the apps I can rewrite the database abstraction in such a way it could switch load balancers (like round-robin DNS) if one was unavailable. I have some Wordpress stuff that isn't mission critical, but my site powercuts.info is the biggest and gets the most traffic, I want to make sure it performs well.

Thank you


texts: Performance of query with complex where clause on 10M rows

texts:
We have a query that destroys our production server when run.

It is part of a reporting functionality and the bad part looks as follows:

SELECT DISTINCT
    mt.ID           AS ID
FROM
    [dbo].[MyTable]    mt
WITH (NOLOCK)
WHERE
    (@aVariable           IS NULL
      OR (CONVERT(VARCHAR(22), mt.Date1, 112) >= CONVERT(VARCHAR(22), @date1, 112))
    AND (@status            IS NULL
    OR @status <> 2
    OR  (   @status = 2
        AND (  SELECT COUNT(*)
               FROM
               MyTable mt2
               WITH (NOLOCK)
               WHERE
                  mt2.CaseID = mt.CaseID
                  AND mt2.Date1 > mt.Date1
             ) = 0
        )
    )
    AND (@aSecondVariable IS NULL
           OR (CONVERT(VARCHAR(22), mt.Date1, 112) <= CONVERT(VARCHAR(22), @date1, 112)))
    AND (@aThirdVariable  IS NULL
           OR (CONVERT(VARCHAR(22), mt.Date2, 112) >= CONVERT(VARCHAR(22), @date2, 112)))
    AND (@aFourtVariable  IS NULL
          OR (CONVERT(VARCHAR(22), mt.Date2, 112) <= CONVERT(VARCHAR(22), @date2, 112)))

Furthermore, the table and indexes are created as follows:

CREATE INDEX [MyIndex] ON [dbo].[MyTable]
    ([AColumn], [AColumn2], [Date1], [Date2])
WITH (FILLFACTOR = 90)

MyTable has about 80 columns and a single column Primary Key: (ID).

The MyTable mt consists of about 10.000.000 rows. There is an index on the columns which contain aVariable, aSecondVariable, aThirdVariable and aFourthVariable. About half of the values of the date columns are null. In the index they are on place 3 and 4.

When we run the query on one server (without users) it performs really well. When we run it on production (with users) it takes too long and times out.

We are wondering how this can be. The execution plans are the same on both servers. We thought that the result might be cached somewhere or that the memory which is free (2GB RAM) is insufficient.

We are no experts on database performance and hope some real DBAs can provide us with their views. Thanks.


texts: I'm trying to use a variable in Oracle PL/SQL, but I'm getting an error. What's wrong here?

texts:
I come from an SQL Server background and I'm trying to figure out how to use Oracle PL/SQL variables.  I'm trying to declare a variable and then select that variable.  It sounds simple, but I am doing something wrong.  Here's my code:

declare
    num number;
begin
    num := 64;
    select num from dual; --error here
end;
/

This gives me an error message that says: "PLS-00428: an INTO clause is expected in this SELECT statement".

I tried a few other permutations of this but each gives me an error:

declare
    num number;
begin
    num := 64;
end;
select num from dual; --"num" invalid identifier
/

And this one also gives me an error.

declare
    num number;
begin
    num := 64;
end;
/
select num from dual; --"num" invalid identifier

Based on Mihai's comment, I tried putting the declare after the begin, but that threw errors as well:

begin
declare
    num number;
    num := 64; --encountered symbol "=" when expecting ...
end;
/
select num from dual; --"num" invalid identifier

For reference: here's how I would do this in SQL Server.  It may not be clear, but the scope of the @num variable below is the file containing the declaration.  This variable is not global.

declare @num int = 64
select @num

I am doing this is DBArtisan.  Note that this question is similar to this one here, but I'm also asking how to use the variable.  And I'm not interested in bind variables (I don't think).  I want to write one-off queries like this: select * from employee where manager_id = mgrID with the mgrID being a variable that I declare.


texts: Managing features for a real estate agency

texts:
I have a database table real_estate where the basic information is held (id, status, sku, address, building_type, sales_status) and now I want to attach features to that real_estate table.

The data collected by the agency always uses the same form; a 7 page form structured in different categories.

For example, you have categories like USAGE, INSPECTION INFORMATION, KITCHEN APPLIANCES etc., and within those categories you have several options for example renovation_year.  Sometimes that option is a single field but other times it's a multiple checkbox or a dropdown or something else.  The form is always the same for every piece of Real Estate.

How would I go about saving that information in the database as normalized as possible?

Should I have a table with real_estate_feature_categories and a table real_estate_feature that has a many-to-one relationship?

How would I link a specific real estate property to a field with a value?


texts: Why does SQL Server say it can't convert varchar to numeric?

texts:
I have the following schema/data in MSSQL Server 2019 (SQLFiddle):

CREATE TABLE products(
    idn NUMERIC(9) PRIMARY KEY
);

CREATE TABLE sales(
    idn NUMERIC(9) PRIMARY KEY,
    pid VARCHAR(50) NOT NULL,
    type VARCHAR(10) NOT NULL
);

INSERT INTO products (idn) VALUES (1);
INSERT INTO sales (idn, pid, type) VALUES (1, 1, 'number');
INSERT INTO sales (idn, pid, type) VALUES (2, 'Colgate', 'word');

sales has mixed data i.e VARCHAR and NUMERIC. The transaction filter takes care of JOINing correctly.

Why does the following SELECT fail (For some reason beyond my control, the generated query has N literals for strings.)?

SELECT
    *
FROM
    products
    INNER JOIN sales ON products.idn = sales.pid
    AND sales.type = N'number'
WHERE
    products.idn in (1);

I don't see why casting NVARCHAR to NUMERIC is an issue:

SELECT CAST (N'1' as NUMERIC);

If I modify the query slightly it works:

SELECT
    *
FROM
    products
    INNER JOIN sales ON products.idn = sales.pid
    AND sales.type = N 'number'
WHERE
    -- Selecting the same data from `sales`.
    sales.pid in (1);

SELECT
    *
FROM
    products
    INNER JOIN sales ON products.idn = sales.pid
    -- Dropping the `N` prefix.
    AND sales.type = 'number'
WHERE
    products.idn in (1);


texts: How to create a Windows user suitable for installing db2 server with extended security?

texts:
I'm trying to install db2 10.5 on Windows Server 2008 not as built-in "Administrator", but as another local user, but it fails to create instance with the symptoms similar to http://www-01.ibm.com/support/docview.wss?uid=swg1IC85663

First it complains it cannot create DB2ADMNS and DB2USERS groups (in fact, the groups appear after installer dies)

Then there's a another mesage:

  The creation of the process for the partitioned instance failed.
  Return value is "4294965337".

I don't have Active Directory. All users are local. Here's how I create the user for installing db2:

net user db2installer s3cret /ADD
net localgroup Administrators db2installer /add

I don's see any difference between "Administrator" and "db2installer". Perhaps "db2installer" should be added to some security policy?


texts: ORA 01031 Insufficient privileges on GRANT SELECT on ALL_CATALOG to a user as system user

texts:
Command:
GRANT SELECT ON ALL_CATALOG TO appdbuser
                *

Error:
ERROR at line 1:
ORA-01031: insufficient privileges

PROBLEM:

I want my Oracle XE system user (<-important) to be able to GRANT SELECT ON
ALL_CATALOG, ALL_CONSTRAINTS, ALL_INDEXES, ALL_OBJECTS, ALL_TABLES,
ALL_TAB_COLUMNS and ALL_VIEWS.

DMBS: Oracle XE 18c (Express Edition), Version 18.4
Client(s): SQL Developer 20.2.0.175 AND also with SQL Plus 12.2.0.0
Operating System: Windows 10 Pro Build 18363
User(s) & Privileges:

The Windows user that I am logged in with is a domain user account which is part of Windows Administrators group on my machine.

I have installed this DB on my own machine with this same user, that I am currently logged in with.

I have also verified that this user is included in ORA_DBA Windows user group. The image below shows a list of other groups on my Windows machine that start with ORA.

I am connected as system user (tried both via sqlplus and sql developer) to Oracle XE DB (pluggable DB not CDB).

I connected to the DB with sys/password as sysdba and executed the following command with success:
GRANT ALL PRIVILEGES TO system;
but the GRANT SELECT ON commands still gives the Insufficient privileges error.

If it matters, I have checked that NTS is included as Authentication service in my sqlnet.ora file like this:
SQLNET.AUTHENTICATION_SERVICES= (NTS)

(EDIT1) As per this answer I have now also tried
GRANT ALL PRIVILEGES TO system WITH GRANT OPTION;
which gives me following error report
Error report -
ORA-01939: only the ADMIN OPTION can be specified
01939. 00000 -  "only the ADMIN OPTION can be specified"
*Cause:    System privileges and roles can only be granted with the admin
option. The grant option cannot be used with system privileges and
roles. The delegate option cannot be used with system privileges.
*Action:   Specify the admin option.

After 7, I tried
GRANT ALL PRIVILEGES TO system WITH ADMIN OPTION;

with success, as in Grant succeeded, but my problem is still there.
More Context

This command is part of a SQL script generated by a batch script, neither of which I want to or can change, as that is used by a larger team and based on given parameters these scripts already work for MS SQL Server and Oracle 12.2.0.1.0 on a Linux machine (however the IT has set it up), which is maintained by our IT and we have limited (2) licenses for it only. Hence, I am setting up Oracle XE on my machine and want something to be repeatable so other devs can just do it from my experience.
When I executed this script by logging in with system/password as sysdba, these GRANT commands inside the SQL script worked, but even this logging in is coded in script, which I don't want and most likely shouldn't change. Its ok if I provide the devs with an additional script for them to execute before they use the standard scripts.
This script in question is also creating the users, to whom the script is now GRANTing rights. There were errors in those CREATE USER commands too, because the CREATE USER command specifies a DEFAULT TABLESPACE user_data and a TEMPORARY TABLESPACE temporary_data. The DB complained about these not existing in database (xepdb1), so I created these tablespaces with system user using following commands:

   CREATE TABLESPACE user_data
       DATAFILE '<ORA_DATA>\XE\XEPDB1\user_data.dbf' SIZE 1M
       AUTOEXTEND ON NEXT 1M;

   CREATE TEMPORARY TABLESPACE temporary_data
       TEMPFILE '<ORA_DATA>\XE\XEPDB1\temporary_data.dbf' SIZE 2M
       AUTOEXTEND ON NEXT 1M;


texts: Transfer of data from table to other database table have the same primary key

texts: I have a database with data, I want to copy the all tables with data to newly created database also with same existed auto incremented primary field. How I can do that process.


texts:
I am using mysql 5.2.44 CE revision 9933

I have a table in which I am trying to update a few columns of the rows.
I am not using queries, just doing it using the edit table UI provided in the workbench.

After I fill in the details and hit apply, mysql doesnt show any errors but it doesn't update the table either. the table uses the InnoDB engine.
can anyone please tell me what the issue might be?


texts: I need to identify a SQL Server 7 in TSQL. I tried serverproperty but it's not supported in this version. I have sp_MSGetVersion but it returns a table and I don't know how I can get only the version string from this SP.


texts:
A little background

We are adding a new feature to our software which allows users to enter text for a specific Customer or Location.  The software will then read these from the database when a Customer or Location is chosen, and display the appropriate text in a pop-up window.  Locations must have a Customer but a Customer can have no Locations.  There are two scenarios that we would show these notifications:

A Customer is selected from a drop down control and the software will look up any notifications for that Customer.
A Location is selected from a drop down control and the software will automatically select the associated Customer and display any notifications for both.

I originally thought about creating two tables: CustomerNotifications and LocationNotifications, but when I added the fields for both, I realized the only difference is one table references a CustomerId while the other references a LocationId:

CustomerNotifications

LocationNotifications

I was thinking of combining the two to look like this:

Combined

Allowing NULL on both CustomerId and LocationId in case the notification is for one specifically.  Is this the best approach or should I keep the table separate?  Will I run into join issues if trying to retrieve both notifications but one is NULL?

My past tells me I have more flexibility with two tables but the "normalization" in me wants to try to condense them.


texts:
I am trying to investigate some concurrency issues on Oracle 11 RAC.
So far the most helpful tool I have is "blocking sessions" view of the Enterprise Manager, which tells me which query is blocking everyone else and what it is waiting for.  However, to take advantage of that view, I need to catch the problem as it is happening.  So I'm looking for a way to query oracle for historic data which would give me data similar to what "blocking sessions" screen has to offer in Enterprise Manager.


texts: join table and count them SQL based on segment

texts:
I have 2 tables and I'm trying to join and count how many people have answer
each questions. I'm trying to find a better way to have only two column. the
first one will be of people who answer "Just Me,My Spouse/Partner" and the
other column who people who answer Children 0-5yr, 6-12yr,13-17yr. I'm
trying to figure out a better way to have a table that looks like this

Question                  Answer               Children     Justme/partner
do you buy milk           weekly                400            20
do you consume cheese     daily                 15             300
how many time you drive   daily                 400            220

here are the 2 tables
This is the table called "household" that is provided:

User    JustMe      Children 0-5      Children 6-12    Myspouse/partner
  5      N              N               N                  N
  5      N              N               N                  N
  4      N              N               N                  N
  4      N              N               N                  N
 12      N              N               N                  N
 12     Just Me         N               N              Myspousepartner
 46     Just Me         N        Children 6-12 years   Mypousepartner
 46     Just Me         N        Children 6-12 years   Mypousepartner

Survey table

 User       questionid   answerid   question                   answer
  4          2             5         how often you buy gas     weekly
  46         3             4         how often you buy milk    monthly
  75         3             4         how often you buy milk     weekly
  13         4             2         how often you use coupon   monthly

I used the

  SELECT id,question, Children_0_5_years, Children_6_12_years,
  Children_13_17_years, count(*) AS NEW_COUNT
  FROM table1 inner join table2 on table1.user=table2.user group by...

unfortunately this query return things that I didn't need.


texts: How do I save my preferences after moving components around in the SSIS Toolbox?

texts:
I'm working with SSIS 2012 (SQL Server Data tools), which runs in the VS 2010 Shell.
This is my first time working on a project that required us to install some 3rd party SSIS components. (SSIS-PowerPack from ZappySys.com)
When the components install, they all appear in the tool box under "Common".
I can move each component to more appropriate sections of the tool box by right clicking and choosing “Move to Other Sources” or “Move to Other Destinations”, however then next time I open the project, they’ve all snapped back to common. How can I save my preferences, or change the defaults so these components stay where I want them?

Edit: Just wante to add that its not strictly related to the 3rd part vendor's components, Microsoft's own OData Source also appears in Common instead of Other Sources after install, and also snaps back to Common on the next project load if you try to move it.


texts: Primary server 'XYZ' can not write to shared drive during availability group configuration

texts:
I am in a process of adding databases to availability group. In one of the steps it was asking "data synchronization prefferences" as shown below

I have put the mapped drive location path, which was FILESHARE created in azure portal.

Now availability group validation fails as shown below, it says "Primary server '[MYNODENAME]' can not write to shared drive during availability group configuration"

Update

IP for listener was wrong . issue resolved .

New Error

  The Windows Server Failover Clustering (WSFC) resource control API returned error code 87.  The WSFC service may not be running or may not be accessible in its current state, or the specified arguments are invalid.  For information about this error code, see "System Error Codes" in the Windows Development documentation.
  The attempt to create the network name and IP address for the listener failed. The WSFC service may not be running or may be inaccessible in its current state, or the values provided for the network name and IP address may be incorrect. Check the state of the WSFC cluster and validate the network name and IP address with the network administrator.
  The attempt to create the network name and IP address for the listener failed. The WSFC service may not be running or may be inaccessible in its current state, or the values provided for the network name and IP address may be incorrect. Check the state of the WSFC cluster and validate the network name and IP address with the network administrator. (.Net SqlClient Data Provider)

Here is screenshot


texts: Is there any danger in granting SUPER privileges to a user?

texts:
I had problems in restoring procedures in my db, so what I did was to grant SUPER privileges to my dbuser.

This user is the same user used in my web application, so I would like to know if there is any danger in granting SUPER privileges to a user.


texts: How does tablespace sizing works in Oracle

texts:
Let's say I have an input of 20 gigabytes of raw data (text file) which in turn I would into an external table and would then swap partition in order to place them on my database.

The question now is given 20 gigabytes, what should I expect the size of my TEMP/UNDO and USERS (default) tablespace to be when this happens given the following assumptions:

20 gigabytes of data would be placed on one table (partitioned) approximately having 100 million records
I am going to bulk process this data using CTAS (then drop the orignal and rename the new table) as well as do some mass/bulk updates and selects
I am going to place multiple indexes on this table (uniques)
I am not going to compress the table
I am using a 32kb block size for my USERS(or default) tablespace

Given these considerations, I have come up with this sizing estimate and as to why I chose these: (Please correct me if I'm wrong since I am not an experienced DBA)

USERS tablespace (40 gigabyte) ~ I might use a bigfile tablespace data file instead of a regular one. I am assuming that the size of the input text file would be of the same size when I transfer it to a table. The reason why I doubled it is because I am going to use CTAS which from what I understand would be similar to copying the table itself.

TEMP/UNDO tablespaces (20 gigabyte for each) ~ From what I understand, temp is used for sorting data and UNDO would be used for rollback and updates (please correct me if I am wrong about this), and given that most of my queries and update statements would load most if not all of the 100 million records on my table, I am thinking that the exact size for sorting and undo should be the same as the data file size itself. (Again this might be a less educated guess and please correct me if I am mistaken)

Summing that up I would have around 80 gigabytes of tablespaces on my database machine, exclusing the redo logs and the indexes which I think I'll put on another tablespace sizing around 10-20 gigabytes giving me a total of 100 gigabytes

So right now I am looking at 100 gigabytes table space needed for an input of 20 gigabytes.

Is this okay or am I doing something wrong here and blowing the tablespace sizing out of proportion?


texts: How to backup a local database to cloud database?

texts:
I am about to start developing a POS software as my new project with python and electron js. It is going to be a desktop application.
However, my concern is it should work in both cases when desktop is connected to Internet or not.
If I use cloud database services like azure or aws, I'll certainly be able to have a POS software that works online.
If I use local databases, I'll be able to deploy a software that works offline.
What could be the best way so the application works in both cases like:
If its connected to Internet, AWS serves as database.
If it's not, modifications are stored locally and get updated as soon as desktop goes online.

POS Software can be used by stores with multiple branches so data has to be synchronised all the time.

I'll really appreciate any input in this regard :)

This is my first desktop application software on this scale. I've been doing online stores in php uptil now.

Please bear with me if I couldn't express myself precisely.


texts: How does cast work with Set Returning Functions (SRF) like generate_series?

texts:
I got to wondering about how we would best use generate_series if we only want a series of dates. As a note, generate_series returns setof timestamp or setof timestamp with time zone (same as argument type). For example, observe this..

SELECT generate_series(
  '2018-01-01'::date,
  '2019-01-01'::date,
  '1 day'
);
    generate_series
------------------------
 2018-01-01 00:00:00-06
 2018-01-02 00:00:00-06
 2018-01-03 00:00:00-06

In the above you'll notice the time portion. In order to generate dates, I tried,

SELECT cast(
  generate_series(
    '2018-01-01'::date,
    '2019-01-01'::date,
    '1 day'
  )
  AS date
);
 generate_series
-----------------
 2018-01-01
 2018-01-02
 2018-01-03

To my surprise that worked! But, that raises the question, is this supported? If it is, is this documented. Even more awkward, in the above you'll see that cast is returning a setof, but if you move it to the from condition like this,

SELECT *
FROM cast(
  generate_series(
    '2018-01-01'::date,
    '2019-01-01'::date,
    '1 day'
  )
  AS date
);

You'll get back,

  ERROR:  set-valued function called in context that cannot accept a set

As a side note, it works in sub-selects and the dynamic can also be seen with int/reals

SELECT x/5 FROM (SELECT CAST(generate_series(1,10) AS real)) AS t(x);
SELECT x/5 FROM (SELECT CAST(generate_series(1,10) AS int)) AS t(x);

Question inspired from this answer


texts: mongodb is not accessible through IP address 35.x.x.x

texts:
I have installed mongo DB on window server that is accessible on localhost:27017  but not accessible on 35.x.x.x:27017.

I checked:

27017 Port is open.
mongoDB is running.

output of: netstat -anb | findstr :27017

TCP    127.0.0.1:27017        0.0.0.0:0              LISTENING
TCP    127.0.0.1:51186        127.0.0.1:27017        TIME_WAIT

my mongos.conf file is

dbpath = d:\MongoDB\Database
logpath = d:\MongoDB\Logs\log.txt
noauth = true # use 'true' for options that don't take an argument
bind_ip=0.0.0.0


texts: Don't repeat COLUMN_GET in MariaDB dynamic columns WHERE clause

texts:
I have a MariaDB query like this:

select id, column_get(params, 'Variant' as char) as variant
from runs
where column_get(params, 'Variant' as char) in ('Whatever', 'Wherever');

Is there a way to not have to repeat the whole column_get() bit twice?

Edit: Fixed tautological example.


texts: Optimising a query to get rows where x is between pairs of values from another table

texts:
PostgreSQL 9.4.5

In my database there are 2 tables, vcfentries and exons.

I want to query for rows in vcfentries where pos is between a list of pairs of positions, exonstart and exonend in the table exons.

Result of the query:
select * from exons where exons.genename = 'NM_001301824'

Total query runtime: 11 ms.
8 rows retrieved.

This is the equivalent of pulling up the results for a single exon without any joins across tables:

Select pos,chrom,alt,ref from vcfentries
where chrom = '1'
and pos > 33546679
and pos < 33547159

Total query runtime: 11 ms.
89 rows retrieved.

Currently these are the indexes I have on the tables

Querying for a specific exon is efficient:

select vcfentries.pos,vcfentries.chrom,vcfentries.alt,vcfentries.ref,exons.exonnumber from exons , vcfentries
where vcfentries.pos BETWEEN  exons.exonstart
and exons.exonend
and exons.genename = 'NM_001301824'
and exonnumber = 6
and vcfentries.chrom = exons.chrom

Total query runtime: 72 ms.
137 rows retrieved.

"Nested Loop  (cost=438468.31..2324377.64 rows=809587 width=24)"
"  ->  Index Only Scan using exonspkey on exons  (cost=0.42..8.44 rows=1 width=26)"
"        Index Cond: ((genename = 'NM_001301824'::text) AND (exonnumber = 8))"
"  ->  Bitmap Heap Scan on vcfentries  (cost=438467.89..2317823.87 rows=654532 width=16)"
"        Recheck Cond: ((pos >= exons.exonstart) AND (pos <= exons.exonend) AND (chrom = exons.chrom))"
"        ->  Bitmap Index Scan on vcfentries_pos_chrom_idx  (cost=0.00..438304.26 rows=654532 width=0)"
"              Index Cond: ((pos >= exons.exonstart) AND (pos <= exons.exonend) AND (chrom = exons.chrom))"

The performance goes down the toilet when querying for them all. Suddenly it jumps into the minutes range:

select * from exons , vcfentries
where vcfentries.pos BETWEEN  exons.exonstart
and exons.exonend
and exons.genename = 'NM_001037501'
and vcfentries.chrom = exons.chrom

Total query runtime: 325389 ms.
2331 rows retrieved.

"Hash Join  (cost=58.73..11528494.14 rows=11334216 width=24)"
"  Output: vcfentries.pos, vcfentries.chrom, vcfentries.alt, vcfentries.ref, exons.exonnumber"
"  Hash Cond: (vcfentries.chrom = exons.chrom)"
"  Join Filter: ((vcfentries.pos >= exons.exonstart) AND (vcfentries.pos <= exons.exonend))"
"  ->  Seq Scan on coeus.vcfentries  (cost=0.00..7170736.76 rows=141378976 width=16)"
"        Output: vcfentries.pos, vcfentries.chrom, vcfentries.alt, vcfentries.ref, vcfentries.analysisid, vcfentries.filter, vcfentries.info_ac, vcfentries.info_af, vcfentries.info_an, vcfentries.info_baseqranksum, vcfentries.info_clippingranksum, vcfentrie (...)"
"  ->  Hash  (cost=58.56..58.56 rows=14 width=26)"
"        Output: exons.exonnumber, exons.exonstart, exons.exonend, exons.chrom"
"        ->  Bitmap Heap Scan on coeus.exons  (cost=4.53..58.56 rows=14 width=26)"
"              Output: exons.exonnumber, exons.exonstart, exons.exonend, exons.chrom"
"              Recheck Cond: (exons.genename = 'NM_001301824'::text)"
"              ->  Bitmap Index Scan on exons_genename_idx  (cost=0.00..4.53 rows=14 width=0)"
"                    Index Cond: (exons.genename = 'NM_001301824'::text)"

The exons table is modest in size with only about 400K rows.

The vcfentries table is large with a few hundred million rows but is acceptably fast to query with indexes.

I'm having trouble optimizing this query. When I run explain on my attempts to use explicit joins I get the same execution plan.

Any ideas why it's creating such a bad execution plan and any suggested fixes or better queries?


texts: How to make MySQL use indexes in an integer range select query


texts: I lost my database in SQL server 2008 R2

texts:
My database suddenly stopped working and from checking the error logs it says that the master database was corrupted.

So I tried rebuilding the system database with the command:

setup /ACTION=REBUILDDATABASE   /QUIET
      /INSTANCENAME=<instance name>  /SQLSYSADMINACCOUNTS=<accounts>

After running the command, the database server has started working again, but all my database are nowhere to be found. I really need some ideas as to I have no backup databases.

I have the idea that the database files (mdf, ldf, ndf) are still on my local drive so is it possible to relink those files to a database?

update

I tried creating a database with the same name on the instance and got the error

Create failed for Database 'db'.  (Microsoft.SqlServer.Smo)

Cannot create file 'D:\Program Files\Microsoft SQL Server\MSSQL10_50.db\MSSQL\DATA\db.mdf' because it already exists. Change the file path or the file name, and retry the operation.
CREATE DATABASE failed. Some file names listed could not be created. Check related errors. (Microsoft SQL Server, Error: 5170)

Which proves my idea that the files are still there. So how do I rebuild/recover this to my database?


texts: Postgres 9.2 under FreeBSD 8.4 - How do I find the databases & tables?

texts:
I'm trying to create an osm tileserver and am at the point where i've created the database to hold the output of osm2pgsql.  But I can find neither the database (map_data) nor, of course, its tables.  I need to softlink (ln -s) the tables to at least one other spindle, which has nearly a full TB of space open.  I look in pgsql/data/base and see a lot of subtrees with numeric names.  Presumably one of them is my map_data, but I can't find a name:number mapping table.

I'm used to MariaDB/MySQL, which seems much more straightforward to me.


texts:
How does one change the default socket used by mysqladmin?

I don't see a section for [mysqladmin] in /etc/my.cnf.

It tries by default to connect to /tmp/mysql.sock, however, this is not the socket that my MySQL server uses, so I get the error:

error: 'Can't connect to local MySQL server through
    socket '/tmp/mysql.sock'

I know you can specify the socket at the command line, such as:

mysqladmin --socket='/some/other/path/mysql.sock'

How do I change the default socket used by mysqladmin so I don't have to specify my non-standard path each time?


texts: Fastest way to extract full table in Postgres

texts:
I'm trying to dump a table with 50M records to a file, and my goal is to reduce the time in which this action is performed. I usually use the COPY metrics TO 'metrics.csv' DELIMITER ',' CSV; This could take like an hour in the best cases. I'm also interested in exporting the data in some plain format(avoid using pd_dump directories).

One of the ideas is to somehow access this table by a condition or cursor that splits the whole table into equal size pieces, so you can perform for example 2 copy queries at the same time reducing the time to the half.

Example:

COPY (SELECT * FROM metrics WHERE id < 25000000) TO 'metrics_1.csv' DELIMITER ',' CSV;
COPY (SELECT * FROM metrics WHERE id >= 25000000) TO 'metrics_2.csv' DELIMITER ',' CSV;

Could partial indexes created on those conditions help?

Any ideas a good way to implement this partial copy dumps of a table? Is there any other solution to dump this table faster?

Postgresql 11 / 100GB RAM / 20 cores.

After some parallelization with COPY IO bounds doesn't seem to be the bottleneck.


texts: Is it normal that I have to FLUSH QUERY CACHE once every 6 hours to maintain fast queries?

texts:
I am running MySQL 5.5.37.  I have a cronjob set to run every 6 hours to flush the query cache.  I realized that if I don't do that, then the cache becomes fragmented and average query response time could double or triple.  95% of my data is saved in MyISAM tables.

My average response time is between 15ms to 30ms as long as I have the cronjob run.

Is this normal behavior?  Or does it indicate that I have an underlying issue that should be fixed?  Perhaps my query_cache_min_res_unit value is too low, which is causing fragmentation?  Or is fragmentation unavoidable and flushing the cache is perfectly normal?

QUERY CACHE
Query cache is enabled
Current query_cache_size = 4.08 G
Current query_cache_used = 1.05 G
Current query_cache_limit = 3.24 G
Current Query cache Memory fill ratio = 25.79 %
Current query_cache_min_res_unit = 4 K


texts: How to design sales model for escrow type payment process

texts:
I need advice for best sales model for payment process. Basically my system is kind of market place. There is two kind of transaction.

Customer will pay full money to administrator.

Administrator will take some fee and release payment to seller.

So for the single order, we need to do two transaction. But Paypal offers Delayed Chained payment option to do in single transaction as below.

But I am not sure other major payment gateways are proving this option or not. So I want to design the database compatibly for both.

Note: I want to notify customer after paid at this time I don't know
who will be seller. After certain point of time administrator will
choose the seller and admin will release the pay to seller. Here I
want send notification for both seller and customer.

From above, I need to use unique order_id for both transaction. So should i split the order into two and store as two records or can I manage it in single order row. Advice me reliable and better design pattern for this two kind of situation.
What I tried !

Help me out!


texts:
using SQL 2008 R2 with SP2 with a server with 64 GB of RAM. I have set max server memory to 58GB considering SQL Server is limited to only database engine related service only.
I have a question related to setting min server memory

If I set min server memory to 48 GB does that mean

a) If windows sends low memory notification to SQL -- SQL server will trims all its pool and try to maintain memory usage up to 48 GB of memory?

b) In case of aggressive working set trimming by windows -- would it leave SQL server's memory usage to 48 GB setting a "floor" for SQL's memory usage.

c) Are there any recommendation on min server memory?

Searching the web -- internet has plenty of information for max server memory but only few place I read (Brent Ozar blog for configuration best practices) to set min server memory to 50% of total available RAM on the server.


texts: Why is this query not using my nonclustered index, and how can I make it?

texts:
As follow up to this question about increasing query performance, I'd like to know if there is a way to make my index used by default.

This query runs in about 2.5 seconds:

SELECT TOP 1000 * FROM [CIA_WIZ].[dbo].[Heartbeats]
WHERE [DateEntered] BETWEEN '2011-08-30' and '2011-08-31';

This one runs in about 33ms:

SELECT TOP 1000 * FROM [CIA_WIZ].[dbo].[Heartbeats]
WHERE [DateEntered] BETWEEN '2011-08-30' and '2011-08-31'
ORDER BY [DateEntered], [DeviceID];

There is a clustered index on the [ID] field (pk) and there is a non clustered index on [DateEntered],[DeviceID]. The first query uses the clustered index, the second query uses my non-clustered index. My question is two parts:

Why, since both queries have a WHERE clause on the [DateEntered] field, does the server use the clustered index on the first, but not the second?
How can I make the non clustered index be used by default on this query even without the orderby? (Or why would I not want that behavior?)


texts: PostgreSQL streaming replication broken: invalid magic number, out-of-sequence timeline ID

texts:
For some reason, my PostgreSQL slave no longer streaming-replicates changes on the master. It was working before, for a while, but I recently noticed that the slave's database contents is old, and there are errors in the slave's log files, namely "invalid magic number 0000" and "out-of-sequence timeline ID 1 (after 2)".

Do you have any idea how can I troubleshoot this? Or why does it happen?

Details follow.

Log file error messages: (on the slave, pg_log/postgresql-Sat.log)

LOG:  entering standby mode
LOG:  redo starts at 0/1C78848
LOG:  consistent recovery state reached at 0/1C7FA30
LOG:  database system is ready to accept read only connections
LOG:  invalid magic number 0000 in log file 0, segment 1, offset 14942208
LOG:  streaming replication successfully connected to primary
LOG:  out-of-sequence timeline ID 1 (after 2) in log file 0, segment 1, offset 0
FATAL:  terminating walreceiver process due to administrator command
LOG:  out-of-sequence timeline ID 1 (after 2) in log file 0, segment 1, offset 0
LOG:  out-of-sequence timeline ID 1 (after 2) in log file 0, segment 1, offset 0
LOG:  out-of-sequence timeline ID 1 (after 2) in log file 0, segment 1, offset 0
...

Transaction logs: (on the slave -- weird, log file 0...2...1 is from January 2012, but 0...2...2 is from December 2011!?)

-bash-4.1$ tree -D pg_xlog/
pg_xlog/
├── [Dec  6  6:27]  000000010000000000000001
├── [Jan 20 21:33]  000000020000000000000001
├── [Dec 21  5:29]  000000020000000000000002
├── [Dec  6  6:30]  00000002.history
└── [Dec  6  6:30]  archive_status
    └── [Dec  6  6:30]  00000002.history.ready

1 directory, 5 files
-bash-4.1$ cat pg_xlog/00000002.history
1       000000010000000000000001        no recovery target specified

Transaction logs: (on the master)

-bash-4.1$ tree -D pg_xlog/
pg_xlog/
├── [Dec  6  6:27]  000000010000000000000001
├── [Jan 21  8:55]  000000020000000000000001
├── [Dec 21  5:31]  000000020000000000000002
├── [Dec  6  6:30]  00000002.history
└── [Dec  6  6:30]  archive_status
    └── [Dec  6  6:30]  00000002.history.ready

-bash-4.1$ cat pg_xlog/00000002.history
1       000000010000000000000001        no recovery target specified

Config files: (on the slave)

---postgresql.conf---
wal_level = hot_standby
...
hot_standby = on
...

---recovery.conf---
standby_mode = 'on'
primary_conninfo = 'host=dw0azewdbpv11danny user=replicator password=...'
recovery_target_timeline = 'latest'

Finally, on the slave, ps aux shows that:

Jan20   0:01 postgres: startup process   waiting for 000000020000000000000001

Update one week later:

There seems to be a replication bug for PostgreSQL builds with gcc 4.6.0.
http://archives.postgresql.org/pgsql-general/2011-07/msg00686.php
and
http://git.postgresql.org/gitweb/?p=postgresql.git;a=commitdiff;h=45d792f70272ed57b932816562f31c2f79426c2a
However I'd guess that that bug would not affect 9.1?

PostgreSQL versions should be identical on master and slave, and SELECT version() looks like this on both master and slave:

-bash-4.1$ psql
psql (9.1.1)
Type "help" for help.

postgres=# SELECT version();
                                                   version
--------------------------------------------------------------------------------------------------------------
 PostgreSQL 9.1.1 on x86_64-unknown-linux-gnu, compiled by gcc (GCC) 4.4.5 20110214 (Red Hat 4.4.5-6), 64-bit
(1 row)

Perhaps I should also mention that the current master was a slave, originally, and the current slave was the master, originally. -- I did one failover, to test that it worked okay.

(I'll ask this question on the Postgres mailing lists.)


texts: What is the best way to store optional values in table?

texts:
I have a large table that will be partitioned (about 300 million rows). There are currently 57 columns in the table. At the moment we also have an 'Option' column, which is to hold values that would be null for most records. The column type is varchar (max). In other words, this column is to store values that are certain business characteristics of the row. In the extreme case, when all these values occur, the length of this column will exceed 8000 characters. There may also be 0 characters.
There will be around 300 inserts per second and around 200-250 updates on this table (sometimes update which is also updating Option column (for example add new value).
The question is whether it is a better approach to create new columns for these values (then need to create an additional 63 columns) that would mostly be null for a single row. Or is it just a better idea to keep everything in varchar (max)?


texts: How to fix data discrepancies between columns from two tables

texts:
The problem:
I am working for a retail site that sells various products for cars and trucks. We want to integrate eBay and Amazon API to list our products on these stores. The problem arises when I try to send Vehicle compatibility data.

Example from our DB:

Product ID | Other IDs | Make  | Model
  *number* | *number*  | Ford  | F150 Crew Cab
  *number* | *number*  | Chevy | Silverado 2500/3500
  *number* | *number*  | Dodge | Ram Pickup 2500-5500 HD

Example from eBay Master Vehicle List DB:

 Make      | Model
 Ford      | F-150
 Chevrolet | Silverado 2500
 Dodge     | Ram 2500

As seen from the examples eBay/Amazon doesn't recognize our Models. There is no easy solution like using a "split" function to split Models into Model and Sub-model for example, because every case is different. Sometimes Model fields have Submodel as well, sometimes they have a range of Submodels (2500-5500), sometimes Model is not correct (F150 instead of F-150) etc.

Solution:
Changing every Model in the database to a correct Model format is not an option, because our system already uses these incorrect Models for business logic.

Writing a million exceptions in the code to handle incorrect Models is not very appealing as well.

The only solution I see is to create a new table and fill it manually with correct model names and their corresponding IDs for our products. This would be very time consuming task though.

Could anyone suggest a good solution for this problem?


texts: Storing multiple IDs in column or in new table

texts:
I'm fairly new to the databases and I'm now creating little project where I have a database and I want to store deck of cards into the table where each card has its own ID (for example 52 cards in a deck).

What would be the best way to go about it, performance wise?

I have two ideas:

The table "deck" will have column with varchar where I will store cards in CSV format with help of backend language
New table will be created with 52 columns for every card and store them there

Thanks for any help.


texts: Relation algebra problem with renaming columns

texts:
I am trying to rename a 2 columns after joining them together

ρ mmnr←MM.Kortnr, ebnr←EB.Kortnr π Passagerare.Namn, Passagerare.Personnr,
MM.Kortnr, EB.Kortnr Passagerare ⟕ Passagerare.Personnr =EB.Personnr
EB ⟕ Passagerare.Personnr = MM.Personnr MM

The lab assistant said that there is some issue with how I rename my columns but didnt explain what. What is the problem ?


texts: Oracle UPDATE followed by SELECT in LinqPad

texts:
I'm trying to UPDATE values in an Oracle database table (of salary information) that has no row IDs or a primary key.  I'm writing these statements in LinqPad.

This syntax should work just fine in SQL Server SQL:

UPDATE schedule_amounts
SET ANNUAL_RATE = 44000
WHERE SCHEDULE_ID = 'LCSD'
  and SCHEDULE_NO = 2014
  and SCHEDULE_LEVEL = 100
  and SCHEDULE_STEP = 17

SELECT * FROM schedule_amounts

But the driver LinqPad uses is returning ORA-00933: SQL command not properly ended.

Using multiple WHERE conditions is the only way I can specify the exact row I need to update.  I have Googled Oracle's UPDATE syntax but I just can't seem to find an easy-to-understand example of how to do what, to me, is rather simple in SQL Server.


texts: Restore MySQL dump to a container fails with ERROR 1114 "The table is full"

texts:
I am trying to restore a MySQL dump of size around 18GB to another MySQL server, which is running inside a container using this command:
mysql -h example.com -u user -p matomo < dump.sql

But it fails with an error:

ERROR 1114 (HY000) at line 7238: The table 'piwik_log_link_visit_action' is full

Many other small tables are copied successfully, but while coping this table it fails with above error. The size of this table is more than 2GB.
Based on different suggestions available on Stack Overflow, I tried each one but nothing worked.
I tried adding 'autoextend' to the my.cnf file:
innodb_data_file_path=ibdata1:10M:autoextend

I also tried to increase the tmp_table_size and heap_table_size by adding following parameter to the my.cnf file:
tmp_table_size=2G
max_heap_table_size=2G

Also, I made sure that the server (from where I am running the dump restore command) has enough space (more than 20GB of storage available). But nothing worked.
I tried debugging this more and found that, the docker container where MySQL is running has overlay filesystem of size 5GB which starts getting filled and as soon as it fills 100%, I get above error.
Volume mounted on the container is of more than 30GB size. I am not sure from where this overlay file system is coming in docker. Overlay is something coming from docker I guess, but not sure where I can increase its size.

I can't even go inside the overlay directory and keep deleting or freeing the space. Can anyone please help me here.
my.cnf file
[mysqladmin]
user=user1

[mysqld]
skip_name_resolve
explicit_defaults_for_timestamp
basedir=/opt/bitnami/mariadb
port=3306
tmpdir=/opt/bitnami/mariadb/tmp
socket=/opt/bitnami/mariadb/tmp/mysql.sock
pid_file=/opt/bitnami/mariadb/tmp/mysqld.pid
max_allowed_packet=256MB
bind_address=0.0.0.0
log_error=/opt/bitnami/mariadb/logs/mysqld.log
character_set_server=utf8
collation_server=utf8_general_ci
plugin_dir=/opt/bitnami/mariadb/plugin
innodb_data_file_path=ibdata1:10M:autoextend:max:10G
max_heap_table_size=2G
tmp_table_size=2G

[client]
port=3306
socket=/opt/bitnami/mariadb/tmp/mysql.sock
default_character_set=UTF8
plugin_dir=/opt/bitnami/mariadb/plugin

[manager]
port=3306
socket=/opt/bitnami/mariadb/tmp/mysql.sock
pid_file=/opt/bitnami/mariadb/tmp/mysqld.pid
!include /opt/bitnami/mariadb/conf/bitnami/my_custom.cnf


texts: How to iterate through table and modify entrys in an "if" statement with MySQL

texts:
What I have is a table containing some temperature readings which often jump back and forth.

+------------+----------+-------------+
| timestamp  | sensorID | temperature |
+------------+----------+-------------+
| 1511020195 | 1        | 25          |
| 1511020196 | 1        | 26          |
| 1511020197 | 1        | 25          |
+------------+----------+-------------+

I'd like to iterate through the table and delete some rows if some conditions are met. Here is some pseudocode of what I am trying to do:

int senID = 1;
int timestamps[] = //SELECT `timestamp` FROM `temperature` WHERE `sensorID` = senID
int actentry = sizeof(timestamps);

while(actentry >= 2)
{
    int acttemp = gettemp(actentry);
    int lasttemp = gettemp(actentry-1);
    int secondlasttemp = gettemp(actentry-2);
    if(acttemp == lasttemp)
    {
        //DELETE FROM `temperature` WHERE `timestamp` = timestamps[actentry] AND `sensorID` = senID
    }
    else if((acttemp == secondlasttemp) && ((acttemp == lasttemp+1) || (acttemp == lasttemp -1)))
    {
        //DELETE FROM `temperature` WHERE `timestamp` = timestamps[actentry] AND `sensorID` = senID
        //DELETE FROM `temperature` WHERE `timestamp` = timestamps[actentry-1] AND `sensorID` = senID
    }
    actentry--;
}

int gettemp(int entry)
{
    return //SELECT `temp` FROM `temperature` WHERE `timestamp` = timestamps[entry] AND `sensorID` = senID
}

Is there a way of executing this directly on my MySQL DB?

I asked before but didn't get an answer. Because of that I wrote this pseudocode to give a better explanation of what I am trying to to.

Thank you for your answers in advance!


texts: Is there a bug with PL/Scope in combination with associative arrays?

texts:
I believe I stumbled upon a bug with PL/Scope in combination with associative arrays, but perhaps I overlook something.

I have the following package

create or replace package tq84_pkg_c as

    procedure proc_1;

end tq84_pkg_c;
/

with its body

create or replace package body tq84_pkg_c as

    type num_t is table of number index by varchar2(10);

    procedure proc_2 is begin
        null;
    end proc_2;

    procedure proc_1 is
        v_num num_t;
    begin
        if v_num.exists(1) then
           proc_2;
        end if;
    end proc_1;

end tq84_pkg_c;
/

I compile the the package "with" PL/Scope:

ALTER SESSION SET PLSCOPE_SETTINGS='IDENTIFIERS:ALL';

ALTER PACKAGE TQ84_PKG_C COMPILE;

Then, I query all_identifiers:

select
  name          identifier,
  usage,
  type,
  usage_id,
  usage_context_id,
  line,
  col
from
  all_identifiers
where
  object_name in ('TQ84_PKG_C')
order by
  object_name,
  object_type,
  line,
  col;

With the following result:

IDENTIFIER                     USAGE       TYPE                 USAGE_ID USAGE_CONTEXT_ID       LINE        COL
------------------------------ ----------- ------------------ ---------- ---------------- ---------- ----------
TQ84_PKG_C                     DECLARATION PACKAGE                     1                0          1          9
PROC_1                         DECLARATION PROCEDURE                   2                1          3         15
TQ84_PKG_C                     DEFINITION  PACKAGE                     1                0          1         14
NUM_T                          DECLARATION ASSOCIATIVE ARRAY           2                1          3         10
NUMBER                         REFERENCE   NUMBER DATATYPE             3                2          3         28
VARCHAR2                       REFERENCE   CHARACTER DATATYPE          4                3          3         44
PROC_2                         DEFINITION  PROCEDURE                   6                5          5         15
PROC_2                         DECLARATION PROCEDURE                   5                1          5         15
PROC_1                         DEFINITION  PROCEDURE                   7                1          9         15
V_NUM                          DECLARATION VARIABLE                    8                7         10          9
NUM_T                          REFERENCE   ASSOCIATIVE ARRAY           9                8         10         15
V_NUM                          REFERENCE   VARIABLE                   11               10         12         12
PROC_2                         CALL        PROCEDURE                  12               10         13         12

Now, the problem is with the last two records: Their usage_context_id is 10 and this number is supposed (as I believe) to reference usage_id, yet, there is no record with usage_id=10.

So, is this a bug? Or is there a way to include the missing record into the result set?

I believe the problem is caused by the line if v_num.exists(1) then. If I take this if (and the corresponding end if) out, the problem disappears, and usage_conext_id for PROC_2 references an existing usage_id.


texts: InnoDB : Deadlock from one reader and one writer

texts:
This sort of has me miffed.  One query is doing a select that includes a table that is undergoing a heavy UPDATE, but that update takes under 1/2 a second, and there are no other writers to that table.  So I could see how the reader would "wait" until the update completes but I don't understand why this is considered a "deadlock", where neither the update nor the select can proceed.

Here are some facts :

The table nasd_real has 23,000 rows
Every 15 seconds, an update happens that updates approx 1500 rows
This update takes under 1/2 a second, which I know because I can run it from the command line as the data from the source of the update is still there.
The select is doing an aggregation that joins to nasd_real, the result of that join is inserted int a temp table.

Some settings :

mysql> show global variables like '%tx%';
+---------------+-----------------+
| Variable_name | Value           |
+---------------+-----------------+
| tx_isolation  | REPEATABLE-READ |
+---------------+-----------------+
1 row in set (0.01 sec)

mysql> show global variables like '%autocommit%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| autocommit    | ON    |
+---------------+-------+

I included the output from show engine innodb status\G below.

If anyone has seen this type of problem under similar conditions, I would most interested what you think!

TIA.

Don

------------------------
LATEST DETECTED DEADLOCK
------------------------
120720  6:58:15
*** (1) TRANSACTION:
TRANSACTION 1902337, ACTIVE 0 sec starting index read
mysql tables in use 5, locked 5
LOCK WAIT 24 lock struct(s), heap size 3112, 302 row lock(s)
MySQL thread id 45032, OS thread handle 0x7f87c4720700, query id 76514460     localhost prog Copying to tmp table
insert into t_open_existing
select p.portfolio_id
            , op.position_type_id
            , sum(op.position_size * ifnull(nr.last,today.last))
            , sum(op.position_size
     *(ifnull(nr.last,today.last)-ifnull(nr.prev,today.prev))
                                    * (pt.direction))
from member.portfolio as p
 , today
 , position_types as pt
 , member.open_positions as op
LEFT JOIN nasd_real AS nr
 ON   ( op.symbol_id = nr.symbol_id )

 where p.person_id =  NAME_CONST('p_person_id',1)
    and p.portfolio_type_id =  NAME_CONST('l_portfolio_type_id',1)
 and op.portfolio_id = p.portfolio_id
 and today.symbol_id = op.symbol_id
 and op.load_id <  NAME_CONST('l_today',5177)
 and pt.position_type_id = op.position_type_id
 group by p.portfolio_id, op.position_type_id

*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 0 page no 409609 n bits 320 index `PRIMARY` of table `    prog`.`nasd_real` trx id 1902337 lock mode S locks rec but not gap waiting
*** (2) TRANSACTION:
TRANSACTION 190232C, ACTIVE 0 sec starting index read
mysql tables in use 1, locked 1
57 lock struct(s), heap size 14776, 942 row lock(s)
MySQL thread id 42342, OS thread handle 0x7f87c4567700, query id
  76514371     localhost prog Sending data
update  nasd_real,
            stream_last
 set             nasd_real.last_updated = stream_last.last_updated,
            nasd_real.volume = stream_last.volume,
            nasd_real.last = stream_last.last,
            nasd_real.high = stream_last.high,
            nasd_real.low = stream_last.low
where   nasd_real.symbol_id = stream_last.symbol_id

*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 0 page no 409609 n bits 320 index `PRIMARY` of
 table     `prog`.`nasd_real` trx id 190232C lock_mode X locks rec but not gap
*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 0 page no 409604 n bits 320 index `PRIMARY` of
table
`prog`.`nasd_real` trx id 190232C lock_mode X locks rec but not gap waiting
*** WE ROLL BACK TRANSACTION (1)
------------
TRANSACTIONS
------------


texts:
I have a question about getting results after limiting the rows.

I don't have much experience with MySQL, but I'm trying to get results from a database where I need to do the where clause after limiting the results.

SELECT
  COUNT(measurement) as 'measurement errors'
FROM measurelog
WHERE (measurement < (baseline - tolerance))
ORDER BY Id DESC
LIMIT 100

I only need the measurement values of the last 100 rows, so I expect to get about 5~10 results, but now the programm counts untill it has found 100 faulty measurements.

I've tried to switch the where clause to the bottom, but then I get an syntax error from the MySQL client.

Is there something I'm missing?
Is there another way to do this?


texts: MySQL not using primary key on inner join

texts:
I have two tables:

Table 1:

CREATE TABLE `lk_transaction_types` (
  `transactionTypeID` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `isActive` tinyint(2) unsigned NOT NULL,
  `code` varchar(8) NOT NULL,
  `description` varchar(150) NOT NULL,
  `isInbound` tinyint(2) unsigned NOT NULL DEFAULT '1',
  `isOutbound` tinyint(2) unsigned NOT NULL DEFAULT '1',
  PRIMARY KEY (`transactionTypeID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Table 2:

CREATE TABLE `ediLoad` (
  `loadID` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `processID` int(10) unsigned NOT NULL,
  `success` tinyint(2) unsigned NOT NULL DEFAULT '0',
  `transactionTypeID` tinyint(2) unsigned DEFAULT NULL,
  `escoID` int(10) unsigned DEFAULT NULL,
  `ldcID` int(10) unsigned DEFAULT NULL,
  `commodityType` tinyint(3) unsigned NOT NULL DEFAULT '0',
  `filename` varchar(150) NOT NULL,
  `loadDate` datetime NOT NULL,
  `processed` tinyint(2) unsigned NOT NULL DEFAULT '0',
  `processedDate` datetime DEFAULT NULL,
  `dataApplied` tinyint(2) unsigned NOT NULL DEFAULT '0',
  `dataAppliedDate` datetime DEFAULT NULL,
  `errorID` tinyint(3) unsigned DEFAULT NULL,
  `error` tinyint(2) unsigned DEFAULT '0',
  `warning` tinyint(2) unsigned DEFAULT '0',
  PRIMARY KEY (`loadID`),
  KEY `idx_processID` (`processID`,`transactionTypeID`,`escoID`),
  KEY `idx_escoID` (`escoID`),
  KEY `idx_filename` (`success`,`filename`),
  KEY `idx_bulk` (`processed`,`loadDate`),
  KEY `idx_loadDate` (`loadDate`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

When trying to run a simple query it is not using the Primary Key on the lk_transaction_types table:

SELECT COUNT(0)
FROM edi.ediLoad l
INNER JOIN edi.lk_transaction_types lk
  ON  lk.transactionTypeID = l.transactionTypeID
WHERE l.escoID = 2
AND lk.isActive = 1
AND lk.isInbound = 1;

The Query is very slow. So I run explain and get this:

+----+-------------+-------+------------+------+-----------------------------------+------------------------+---------+--------------------------+------+----------+------------------------------------+
| id | select_type | table | partitions | type | possible_keys                     | key                    | key_len | ref                      | rows | filtered | Extra                              |
+----+-------------+-------+------------+------+-----------------------------------+------------------------+---------+--------------------------+------+----------+------------------------------------+
|  1 | SIMPLE      | lk    | NULL       | ALL  | PRIMARY                           | NULL                   | NULL    | NULL                     |   31 |     3.23 | Using where                        |
|  1 | SIMPLE      | l     | NULL       | ref  | idx_escoID,idx_ transactionTypeID | idx_ transactionTypeID | 2       | edi.lk.transactionTypeID | 7158 |    50.00 | Using index condition; Using where |
+----+-------------+-------+------------+------+-----------------------------------+------------------------+---------+--------------------------+------+----------+------------------------------------+

This seems like a simple query with one join on a primary key. Why is it not using the primary key? I've even tried adding 'FORCE INDEX FOR JOIN (PRIMARY)' to the join and it still doesn't use the primary key. Any help would be great. Thanks!


texts: Only "grant usage", but can still select, drop, create?


texts: Flushing hosts without being able to connect to the host

texts:
I'm seeing this dreaded error:
Host '10.0.12.34' is blocked because of many connection errors; unblock with 'mysqladmin flush-hosts'

The server is an RDS instance running in AWS.  There are VPC rules are such that I can only access the database with a VPN, and every user in my organization "lands" in the VPC from the same IP.  In other words, other users are failing to connect to the database server and are causing "our" IP to get blacklisted by the server.
I've tried running the command with the mysql cli binary on my machine, but since my host is blocked, I can't even connect to run the FLUSH HOSTS command.
Unless I'm missing something, it doesn't look like there's a way to run commands against the DB from the AWS console.  Short of spinning up an EC2 instance in the same VPC and installing the mysql client, is there any other way to flush hosts?


texts:
I have run the query in mysql which consists of approximately 33million records when i have executed the query

select population1 < 200000 from city;
it shows like this after execution

34459393 rows in set (16.04 sec)

My question is what is 16.04 sec because in normal time it has taken morethan 30min but it shows 16.04 sec.
How can i calculate the actual time.


texts:
I want to make a view from these tables. The first inner join works as expected and brings a new column with AuspraegungID  from which I want to connect to the  ArtikelVarianten table

At the moment I get a view with all ID that have a counterpart in ArtikelVarianten. But some articles dont have that ID (its set to zero, and no match in the target table) so they are ommitted in the current selection
But I want to include them, and I thought thats where the full outer join comes in.

Select Distinct [A].[Artikelnummer],
  [A].[Bezeichnung1],
  [S].[AuspraegungID],
  [A].[USER_SkuDe] As [Parent SKU],
  [AV].[USER_SkuDe] As [Varianten SKU]
From [dbo].[KHKArtikel] [A]
  inner Join  [dbo].[LBSysStock] [S]
    On [S].[Artikelnummer] = [A].[Artikelnummer]
  Full Outer Join [dbo].[LBSysArtikelVarianten] [AV]
  On [S].[AuspraegungID] = [AV].[AuspraegungID]
    And [AV].[Artikelnummer] = [A].[Artikelnummer]


texts:
I have an autovacuum (to prevent wraparound) happening on one of my tables for almost 4 days. I'm wondering if there is any way for me to tell if it's not keeping up with the amount of updates/deletes that are happening.

For reference, I am using Postgres 9.6 on Google CloudSQL. They have a known issue where pg_stat_progress_vacuum returns null for everything.

I've also been checking pg_stat_all_tables and have noticed that n_dead_tup for my table is going up instead of down, but I'm not sure if this number would be aware of the autovacuuming that is going on.


texts: In MySQL can I drop a database by moving the directory containing the database?

texts:
We ran ran into a problem where MySQL kept crashing because of corruption in one of our databases.  MySQL would start, crash, and then restart.  We determined that the corruption was in a database containing archive tables (i.e. tables using the archive engine).  We weren't able to drop the database in MySQL because it wouldn't stay up long enough to drop it, so we shut down MySQL and moved the directory containing the database to another location.

This prevented MySQL from crashing, and all of our other databases seem to be working fine.  My question is: is moving the database directory containing the database out of MySQL's data directory a safe way to remove a database?  Is it the same as dropping a database?


texts: PostgreSQL 9.3.13, How do I refresh Materialised Views with different users?

texts:
[I think the root cause of this problem is me not understanding permissions and privileges...]

So, to set the stage, the set up I have is a DB, call it MyDb.

I have two users, spu1 and u1, spu1 is a superuser and u1 a 'regular' user. The owner of MyDb is spu1. I think I should also say that u1 has create databases & create role privileges as inherited from a group role.

I have a schema sch1, which is a user defined schema.

Within this schema I have a table, call it tbl1, and a materialised view, call it mvw1.

The tbl1's owner is the spu1, the mvw1's owner is u1.

The Problem:

In the current set up, as described above, I cannot refresh mvw1 as either u1 or spu1. I simply get the below fun error (which I have googled extensively but not found anything which quite solves for my setup..).

ERROR:  permission denied for relation tbl1
********** Error **********

ERROR: permission denied for relation tbl1
SQL state: 42501

I have found out that

Changing the owner of mvw1 to the spu1, allows me to refresh as spu1.
Running the below allows me to refresh mvw1 as u1.

I'm trying to find out what the missing permission(s) (ideally the minimum required) I need to grant the regular user, u1, so that I can refresh this view when logged in as them.

The first option, whilst nice to know, doesn't solve my problem.
The second option seems like I'm in effect granting superuser permissions to a non-superuser, or rather granting greater privileges than I need to.

If anyone can explain to me what exactly is going on here (or point out what info I've missed out in the description of my problem needed to solve it), and let me know if my second option is in fact the way to go or of a better alternative?

Many Thanks!


texts:
One of our clients is using our software with merge replication of a database on a SQL Server 2008 R2 machine. There are two production environments in separate geographical locations only one of which is live at any one time, so basically one and live one on standby. Only the live database is updated by teh applications. Every couple of months they failover between the datacentres and the standby environment become the live centre. There is an instance of SQL Server 2008 in each datacentre and merge replication is used to keep them in sync. This was all working ok until the beginning of the year when we started getting replication errors with some lTID columns in various tables that have the Identity property set.

The errors were like this one:

  The insert failed. It conflicted with an identity range check constraint in database 'GateMain', replicated table 'dbo.tGateCalcsLog', column 'lTID'. If the identity column is automatically managed by replication, update the range as follows: for the Publisher, execute sp_adjustpublisheridentityrange; for the Subscriber, run the Distribution Agent or the Merge Agent.

Then after the last failover we noticed we had an issue with the lTID values in one specific table. Our application relies on the lTID value always having incremented in order such that the highest lTID value is always the newest entry in the table. We've found that due to how the identity ranges are being managed by replication that when the system is failed over that the lTID range of the now live database server may have a range of values that are lower than those already present in the table. Is there a way to manage this in merge replication so we can guarantee that the next identity value allocated to the lTID column in greater than any lTID currently in the table? Or do we need to use a different type of replication or possibly mirroring?


texts: Create a user on database A with a procedure from database B (with EXECUTE AS)

texts:
I have 2 logins :

LoginA: role dbcreator + CONNECT ANY DATABASE
LoginB: role sysadmin

I have 2 databases :

dbA: everyone has all rights on it
dbB: only LoginB has rights on it, LoginA is only the owner of that database but no more

I'm wondering if it is possible to create a user (linked to LoginA) in dbB from a stored procedure of dbA.
Considering this stored procedure has to be executed by LoginA.
Maybe some kind of "EXECUTE AS" mechanism could help ?
Also I have issues about how to use a "USE [dbB]" in a stored procedure.

The solution can have some rights modification,
but these can't give securityadmin nor sysadmin roles.

Here is what is done for now but clearly there is a lot of debug and functionalities to go, but the spirit of what I wanna do is in here :

CREATE PROCEDURE [dbB_UserAfterRestore]
AS
BEGIN
    SET NOCOUNT ON;

    USE [dbB]
    CREATE USER [HOSTNAME\LoginA] FOR LOGIN [HOSTNAME\LoginA]
    ALTER ROLE [db_datareader] ADD MEMBER [HOSTNAME\LoginA]
    ALTER ROLE [db_datawriter] ADD MEMBER [HOSTNAME\LoginA]
END
GO


texts:
I have a table with millions of rows and a column that allows NULL values. However no row currently has a NULL value for that column (I can verify this fairly quickly with a query). However when I execute the command

ALTER TABLE MyTable ALTER COLUMN MyColumn BIGINT NOT NULL;

the query takes forever relatively speaking. It actually takes between 10 and 20 minutes, more than twice as long as adding a check constraint. Is there a way to instantly update the table's metadata for that column, especially since I know that no row has a NULL value for that column?


texts: Moved Model Database and now my SQL Server doesn't work

texts:
I moved my Model data and log files:

ALTER DATABASE Model
MODIFY FILE (NAME = 'modeldev', FILENAME = 'D:\Data\model.mdf')

ALTER DATABASE Model
MODIFY FILE (NAME = 'modellog', FILENAME = 'L:\Log_Files\modellog.ldf')

But it seems it has failed and now my SQL Server connection is gone and now SQL Server doesn't seem to work and I would like to know who how I could rectify.

When I try to connect to SQL Server, the message is:

  Cannot connect to X.

  A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server) (.Net SqlClient Data Provider).

I am using SQL Server 2012. Any help would be appreciated.

Thanks.


texts: Where can I find a good tutorial or book for various MySQL performance guidelines?

texts:
Can anyone refer me a good book or a small tutorial for various use cases for database design on MySQL for high load test/high availability scenarios.

It should include the following:

What are the various scenarios/solutions for high availability?
What are the various scenarios for high load and how to cater them
based on reliability, response time, throughput etc.

I am particularly interested if someone knows of a practical experience book for both of these cases.


texts: Same temp table has created by different session and occurs blocking issue. how to avoid it?

texts:
It's a SQL Server 2012 EE.

One session (SID=90) executed a JOIN query, and it's generated a temp table internally by SQL Server.

But another sessions (SID=103, 109, 110,116) from totally different app ran a query, and blocked by above session due to the same temp table!

As a result several blocking occurred. (Pls refer to the screenshot)
How it could be happened? I can't understand and lost mind how to fix it.

The first session executed:

SELECT ... FROM Institution institutio0_
left outer join ImageFile imagefile1_
on institutio0_.ImageFileId=imagefile1_.Id
WHERE institutio0_.Id=@p0

And blocked sessions executed a same query:

select EventClass, LoginName, StartTime, FileName, ObjectID, IndexID, ObjectType, ObjectName, EventSubClass, Success, case when ParentName is not null then ParentName else OwnerName end as OwnerName, .... SCHEMA_NAME( OBJECTPROPERTY( ObjectID, 'SchemaId' ) ) DerivedOwnerName, OBJECT_NAME( ObjectID ) DerivedObjectName
from ::fn_trace_gettable(  @P0 , default )
where StartTime >  @P1
and StartTime <=  @P2
and DatabaseID =  @P3

Any idea?


texts:
I found MariaDB behaviour changing according to the number of JOINs in a query.

In this particular application, when the number of JOINs goes from 30 to 31 the execution plan changes, causing potential problems to the execution time and resources management.

This behaviour doesn't depend on the number of records, but when they are millions, the chosen plan considerably slows down execution using gigabytes of temporary files.

I couldn't find any configuration parameters that drive this behaviour (32 entities in JOIN is a fixed limit?), I don't know if it can be changed.

The test was made on MariaDB 10.2.x, 10.3.x and 10.4.5 RC.

Scripts to reproduce behaviour:

Data definition (with few sample records)
Query definition (the 31th JOIN is commented)
Explain with 30 JOIN
Explain with 31 JOIN

I would like to find out if there are any configuration (or system) parameters that can modify this behaviour, maybe moving the limit forward.

Any help would be greatly appreciated!
Thank you


texts:
Again, another question related to the answer of the one there: Insert with OUTPUT correlated to sub query table

So I ended up with this query, written for SQL Server:

CREATE TABLE tempIDs
( PersonId INT,
  FinancialInstitutionId INT
);

MERGE INTO Person
USING FinancialInstitution AS fi
  ON 1 = 0
WHEN NOT MATCHED THEN
  INSERT (CreationDate, AdministrativeStatus, LastName, Street1, Number1, City1, State1, PostCode1, CountryId1, WorkDirectPhone1, Fax1, Email1)
  VALUES (GetDate(), 'Legal', fi.Name, fi.Street, fi.Number, fi.City, fi.[State], fi.PostCode, fi.CountryId, fi.PhoneNumber, fi.Fax, fi.Email)
OUTPUT inserted.Id, fi.Id INTO tempIDs;

UPDATE fi
SET fi.PersonId = t.PersonId
FROM FinancialInstitution AS fi
  JOIN tempIDs AS t
    ON fi.Id = t.FinancialInstitutionId ;

I am now trying to get an Oracle version working. But I can't find the proper syntax for the RETURNING that is supposed to replace OUTPUT.

One difference between the two is that RETURNING doesn't belong to the MERGE but to INSERT. I don't think it makes much of a difference here.

DECLARE
  TYPE t_TempID IS RECORD (
       PersonId NUMBER(10,0),
       FinancialInstitutionId NUMBER(10,0));
  TYPE t_TempIDs IS TABLE OF t_TempID;
  t_TempIDs tempIDs;
BEGIN
  MERGE INTO Person p
  USING FinancialInstitution fi ON (1 = 0)
  WHEN NOT MATCHED THEN
    INSERT (CreationDate, AdministrativeStatus, LastName, Street1, Number1, City1, State1, PostCode1, CountryId1, WorkDirectPhone1, Fax1, Email1)
    VALUES (sysdate, 'Legal', 'fi.Name', fi.Street, 'fi.Number', fi.City, fi.State, fi.PostCode, fi.CountryId, fi.PhoneNumber, fi.Fax, fi.Email)
    RETURNING p.Id, fi.Id INTO tempIDs;

  UPDATE fi
  SET fi.PersonId = t.PersonId
  FROM FinancialInstitution fi
    JOIN tempIDs t
      ON fi.Id = t.FinancialInstitutionId;
END

Whatever I try, I get

  ORA-00933: SQL command not properly ended

related to the RETURNING line.


texts: Oracle 10.2.0.4 query hundreds times slower than the same query on Oracle 10.2.0.3

texts:
I have an issue with query on Oracle 11.2.0.4.0 (it takes forever to complete and CPU is at 100% all the time) so I was trying to change optimizer_features_enable to previous versions. It worked with 10.2.0.3. Query executed in matter of seconds. It didn't work with 10.2.0.4 and higher versions so the question is which functionality from 10.2.0.4 causes this query to execute for a very long time (now I am running this query on 10.2.0.4 and it's over half an hour already).

Query: pastebin.com/zmzpwYgq

(I would post link with "http://", but I need at least 10 reputation to post 3 links... Great idea.)

Execution plans for above query:

optimizer_features_enable='10.2.0.3' //works well

optimizer_features_enable='10.2.0.4' //works horribly slow

Text versions of execution plans (they differ from above):

optimizer_features_enable='10.2.0.3':
pastebin.com/zu7wf0e5

optimizer_features_enable='10.2.0.4':
pastebin.com/r6xFGv5H

I don't know how to analyze execution plans, but one thing I see is that execution plan on 10.2.0.3, which works well, uses USE_HASH hint and 10.2.0.4, which works badly, doesn't use this.There is no possibility to modify this query. I am running similar, but not the same, queries all the time so tuning one query is not a solution.

There are 3 tables used in this query: OBJECT, SUBJECT and STATEMENT. I tried before to gather statistics on all those tables and nothing changed. Now I did the same, same result. Sample percent was set to 35. I also gathered statistics for entire schema at 35 estimate percent, still nothing.

//EDIT

As I see there is problem with joining tables. When nested loops are used in 10.2.0.4 for joining tables it takes forever to execute. When hashes are used in 10.2.0.3 to join tables it works normally. But parameter hash_join_enabled is deprecated now and it is disabled in 10.2.0.4. So.. what should I do to make joining tables work faster without using hashes?


texts: How to have more than 100 entries in case statement as a variable

texts:
I wrote a case statement with > 100 choices where I am using the same statement in 4 places in a simple query.

The same query twice with a union between them but also is doing a count and therefore the group by also contains the case statement.

This is to relabel some company names where different records for the same company are spelled differently.

I tried to declare a variable as a VarChar(MAX)

declare @CaseForAccountConsolidation varchar(max)

SET @CaseForAccountConsolidation = 'CASE
       WHEN ac.accountName like ''AIR NEW Z%'' THEN ''AIR NEW ZEALAND''
       WHEN ac.accountName LIKE ''AIR BP%'' THEN ''AIR BP''
       WHEN ac.accountName LIKE ''ADDICTION ADVICE%'' THEN ''ADDICTION ADVICE''
       WHEN ac.accountName LIKE ''AIA%'' THEN ''AIA''
       ...

When I went to use it in my select statement - the query just returned the case statement as text and didn't evaluate it.

I also was unable to use it in the group by - I got this error message:

Each GROUP BY expression must contain at least one column that is not an outer reference.

Ideally I would like to have the CASE in just a single place - so that there is no chance of me updating one line and not replicating that elsewhere.

Is there some way of doing this?

I am open to other ways (Like maybe a function - but I am not sure how to use them like this)

Here is a sample of the SELECT I am currently using

SELECT
   SUM(c.charge_amount) AS GSTExcl
   ,dl.FirstDateOfMonth AS MonthBilled
   ,dl.FirstDateOfWeek AS WeekBilled
   ,CASE
       WHEN ac.accountName like 'AIR NEW Z%' THEN 'AIR NEW ZEALAND'
       WHEN ac.accountName LIKE 'AIR BP%' THEN 'AIR BP'
       WHEN ac.accountName LIKE 'ADDICTION ADVICE%' THEN 'ADDICTION ADVICE'
       WHEN ac.accountName LIKE 'AIA%' THEN 'AIA'
       ELSE ac.accountName
   END AS accountName
   ,dl.FinancialYear
   ,CONVERT(Date,c.date_charged) AS date_charged
FROM [accession] a
   LEFT JOIN account_code ac ON a.account_code_id = ac.account_code_id
   LEFT Join charge c ON a.accession_id = c.accession_id
   LEFT JOIN dateLookup dl ON convert(date,c.date_charged) = dl.date
WHERE a.datecreated = CONVERT(DATE,now())
GROUP BY
   dl.FirstDateOfMonth
   ,dl.FinancialYear
   ,dl.FirstDateOfWeek
   ,CONVERT(Date,c.date_charged)
   ,CASE
       WHEN ac.accountName like 'AIR NEW Z%' THEN 'AIR NEW ZEALAND'
       WHEN ac.accountName LIKE 'AIR BP%' THEN 'AIR BP'
       WHEN ac.accountName LIKE 'ADDICTION ADVICE%' THEN 'ADDICTION ADVICE'
       WHEN ac.accountName LIKE 'AIA%' THEN 'AIA'
       ELSE ac.accountName
   END

UNION

SELECT
   SUM(c.charge_amount) AS GSTExcl
   ,dl.FirstDateOfMonth AS MonthBilled
   ,dl.FirstDateOfWeek AS WeekBilled
   ,CASE
       WHEN ac.accountName like 'AIR NEW Z%' THEN 'AIR NEW ZEALAND'
       WHEN ac.accountName LIKE 'AIR BP%' THEN 'AIR BP'
       WHEN ac.accountName LIKE 'ADDICTION ADVICE%' THEN 'ADDICTION ADVICE'
       WHEN ac.accountName LIKE 'AIA%' THEN 'AIA'
       ELSE ac.accountName
   END AS accountName
   ,dl.FinancialYear
   ,CONVERT(Date,c.date_charged) AS date_charged
FROM [accession] a
   LEFT JOIN account_code ac ON a.account_code_id = ac.account_code_id
   LEFT Join charge c ON a.accession_id = c.accession_id
   LEFT JOIN dateLookup dl ON convert(date,c.date_charged) = dl.date
WHERE a.datecreated = DATEADD(YEAR,-1,CONVERT(DATE,now()))
GROUP BY
   dl.FirstDateOfMonth
   ,dl.FinancialYear
   ,dl.FirstDateOfWeek
   ,CONVERT(Date,c.date_charged)
   ,CASE
       WHEN ac.accountName like 'AIR NEW Z%' THEN 'AIR NEW ZEALAND'
       WHEN ac.accountName LIKE 'AIR BP%' THEN 'AIR BP'
       WHEN ac.accountName LIKE 'ADDICTION ADVICE%' THEN 'ADDICTION ADVICE'
       WHEN ac.accountName LIKE 'AIA%' THEN 'AIA'
       ELSE ac.accountName
   END

The purpose for this UNION is to return all data for a timeperiod, and ALSO to return data  for the same timeperiod for 12 months previously

EDIT:  Added a missing "CATCH-ALL"
EDIT2: Added a second ½ of the UNION statement
EDIT3: Corrected the GROUP BY to include some other necessary elements


texts:
Good morning, please, has anyone used logins audit without using the Profile?

My client needs an audit that displays the logins that connected to the bank and after they disconnected.

In the case the profiler believe it is too heavy to keep active all the time collecting.

Do you have anything that might help us in this case?

thank you
Carol Lavecchia


texts:
Given any version of Oracle:

How do I find my current SCN?
What is the maximum possible SCN?


texts: Circular referencing required for class?

texts:
I'm taking a class called Intro To Database Systems.  I'm given a diagram and I need to create tables for it in SQL server.  However, the diagram appears to have circular referencing.  Am I correct in thinking that?  If so, how do I implement it?  Here is the diagram.

As you can see, DNO in EMPLOYEE references DNUMBER in DEPARTMENT, and MGRSSN in DEPARTMENT references SSN in EMPLOYEE.

Here is my code so far, which doesn't work for obvious reasons.

CREATE TABLE EMPLOYEE (
FNAME   Char(20)    NOT NULL,
MiNIT   Char(1)     NULL,
LNAME   Char(20)    NOT NULL,
SSN     Integer     NOT NULL,
BDATE   Date        NOT NULL,
E_ADDRESS   Char(35)NOT NULL,
SEX     Char(1)     NOT NULL,
SALARY  Money       NOT NULL,
SUPERSSN    Integer NULL,
DNO     Integer     NOT NULL,
CONSTRAINT EMPLOYEE_PK PRIMARY KEY (SSN),
CONSTRAINT DNO_Relationship FOREIGN KEY (DNO)
REFERENCES DEPARTMENT(DNUMBER),
CONSTRAINT SUPERSSN_Relationship FOREIGN KEY (SUPERSSN)
REFERENCES EMPLOYEE(SSN)
);

CREATE TABLE DEPARTMENT (
DNAME   Char(20)    NOT NULL,
DNUMBER Integer     NOT NULL,
MGR_SSN Integer     NOT NULL,
MGR_START_DATE  Date    NOT NULL,
CONSTRAINT DEPARTMENT_PK PRIMARY KEY (DNUMBER),
CONSTRAINT MGR_SSN_Relationship FOREIGN KEY(MGR_SSN)
REFERENCES EMPLOYEE(SSN)
);


texts: I am getting below error intermittently while running ssis package through sql agent job for sql server 2012 can anyone help me?

texts:
I am getting below error intermittently while running ssis package through sql agent job  for sql server 2012  ERROR:-"Argument “OFF” for option “checkpointing” is not valid Argument “OFF” for option “checkpointing” is not valid.  The command line parameters are invalid."
Below is command line /DTS ""\MSDB\BIPS\CB\PACKAGENAME"" /SERVER SERVERNAME  /CHECKPOINTING OFF /REPORTING E


texts: Whats is the right way to check for new messages?

texts:
I have some messages in my mails table (MySQL).

I would like to send request via ajax every 5 seconds and look for new messages.

when i load the page first time, I use query like this

SELECT * FROM mails WHERE user_id = 1 LIMIT 100;

I'm displaying the messages in frontend as html table.

If any new messages received, how to only fetch new messages via AJAX?

I'm talking about the same way how gmail inbox works. When someone send you a mail, new messages is prepended in your browser mail list.

Thanks


texts:
I have an event table that is partitioned by month on EventCreatedDateTime and contains 1bn+ rows.
CREATE TABLE [dbo].[Events](
    [EventtId] [bigint] IDENTITY(1,1) NOT NULL,
    [EventCreatedDateTime] [datetime] NOT NULL,
    ...
    ...
    [SystemCreatedDt] [datetime] NOT NULL,
    [SystemUpdatedDt] [datetime] NOT NULL,
 CONSTRAINT [PK_dbo_Events_EventCreatedDateTime_EventtId] PRIMARY KEY CLUSTERED
(
    [EventCreatedDateTime] ASC,
    [EventtId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [ps_monthly_date]([EventCreatedDateTime])
) ON [ps_monthly_date]([EventCreatedDateTime])

If I query the table using SystemCreatedDt or SystemUpdatedDt in the WHERE clause I can't seem to get any partition elimination and the query plan shows a clustered index scan of all 1bn rows, even if I create a partition aligned index on SystemCreatedDt or SystemUpdatedDt.
SELECT
        EventtId
        ,EventCreatedDateTime
        ...
        ...
       ,SystemCreatedDt
       ,SystemUpdatedDt
 FROM dbo.Events
WHERE SystemCreatedDt > '2015-01-01 00:00:00' AND SystemCreatedDt <= '2015-01-02 00:00:00'

Struggling to tune this query and make use of the partitioning so I would be grateful of any help with index tuning a partitioned table.
Edit 26/01/2017
Here is an anoymized plan for the query https://www.brentozar.com/pastetheplan/?id=HyvMM5wPe
To give some context to this
Column1 = EventtId
Column5 = EventCreatedDateTime
Column18 = SystemCreatedDt
Column20 = SystemUpdatedDt
I had to use SSMS 2014 to create a plan that I could paste into pastetheplan. One bit of info missing from this for the index scan is Estimated Number of rows to be read which is 1,173,200,000.
There is a suggested missing index for all 20 columns in the query, but that wouldn't give us any partition elimination I don't think.
Edit 27/01/2017
DDL for Index
CREATE NONCLUSTERED INDEX [IX_dbo_Events_SystemCreatedDt] ON [dbo].[Events]
(
    [SystemCreatedDt] ASC,
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [ps_monthly_date]([EventCreatedDateTime])
GO


texts: How to extract xml attribute from a column?

texts:
I have a column called target_data in the sys.dm_xe_session_targets that returns the path where the pertinent file resides in the format of xml tags.
This data is not in XML datatype. One has to cast it to the XML data type.

This is how the typical field in the column looks like:

  <EventFileTarget truncated="0">
  <Buffers logged="135" dropped="0"/>
  <File name="C:\ExtendedEvents\degree_of_par\deg_of_par_0_130897981436560000.xel"/>
  </EventFileTarget>

I want to retrieve the name attribute of the file tag i.e. C:\ExtendedEvents\degree_of_par\deg_of_par_0_130897981436560000.xel in this case.

I tried the following script for the same, however it is throwing errors:

select xe.m.value('EventFileTarget/file/@name[1]','nvarchar(500)'),*
from sys.dm_xe_session_targets as s
cross apply (select cast(target_data as xml) as m) as xe

The error reads :

  Msg 2389, Level 16, State 1, Line 1 XQuery [xe.m.value()]: 'value()'
  requires a singleton (or empty sequence), found operand of type
  'xdt:untypedAtomic *'

I'm perplexed. Please help