Upgrading SQL Server with Integration Services We have a database running SQL Server 2008R2 which requires an upgrade (to SQL Server 2014) because it is a pre-requisite for the latest edition of the software which the database is the back end of.

Integration Services (2008R2) is also running on this server, with many .dtsx packages stored in MSDB. They are mainly used for ETL purposes and transforming data for reporting reasons.

Due to the nature of the environment, it will need to be an in-place upgrade. The database engine upgrade will be a breeze, but I was wondering specifically about the .dtsx packages in MSDB. Will they automatically upgrade when the database engine and integration services is upgraded, or will there be a need to manually upgrade them after this.

Are there any known issues with upgrading Integration Services between 2008R2 and 2014 SP2?

Fortunately we have a mirrored environment running in a VM which we can mess around with in terms of testing the application (and packages) before we move to Live.

Thanks

As per BOL

  When you upgrade to SQL Server 2014 Integration Services (SSIS) on a
  machine that has one of these earlier versions of Integration Services
  installed, SQL Server 2014 Integration Services (SSIS) is installed
  side-by-side with the earlier version.

It also says that:

You can upgrade by using one of the following methods: Run SQL Server
2014 Setup and select the option to Upgrade from SQL Server 2005, SQL
Server 2008 or SQL Server 2008 R2, or SQL Server 2012.
Run setup.exe at the command prompt and specify the /ACTION=upgrade
option. For more information, see the section, "Installation Scripts
for Integration Services," in Install SQL Server 2014 from the
Command Prompt.

Source

Now, as per my personal experience with In-Place SQL Server upgrade from SQL Server 2008 R2 to SQL Server 2012 I didn't face compatibility issues. Packages ran fine, except for few where I had to open packages and compile script task again.


How to reject unvalidated SSL client connections from the MongoDB server? I have setup a MongoDB server with a self-signed server certificate, and when I use the server validation method in Compass along with the CA certificate, it works well. However, I am still able to connect to the database if I select the “unvalidated” option in Compass, and I don’t want that. I want to reject any client connections that are not validating the server certificate. Is it possible to setup the server to do that? I have requireTLS setup in the config file, but Compass still lets unvalidated connections through.

There was an issue opened on MongoDB Jira: https://jira.mongodb.org/browse/COMPASS-3369

TL;DR Compass allows connections with invalid certificates and it won't be fixed.


"ORA-01033: Oracle initialization or shutdown in progress" error after PC restart Yesterday I installed Oracle 12c database on my PC and configured it like in this guide. Then I created two connections in SQL Developer: first one with a system user, then I created another user and made the second connection with that user. I managed to establish both connections. I even made some sample table using the second one. Well, everything worked great until I restarted my PC. After that I tried to establish a connection, but no luck with either of them. The following error message was displayed:

I simply don't get it. I restarted my PC normally, restarted Oracle services, also did a db shutdown and startup... nothing. Running select instance_name, status, database_status from v$instance; from SQL Plus says OPEN and ACTIVE.

Any suggestion? I'm simply running out of options...

The tutorial you linked uses the multitenant architecture with pluggable databases. I quickly read through the tutorial, and it does not cover this topic, but pluggable databases do not open automatically with the root container, unless specified otherwise. My guess is that you tried to connect the pdborcl pluggable database. Run the below:

select name, open_mode from v$pdbs;

If you see pdborcl, with open_mode = MOUNTED, issue the below:

alter pluggable database pdborcl open;

Now try connecting again. If your database version is 12.1.0.2, you can use the below after opening the pluggable database to make it start automatically the next time the whole database is restarted:

alter pluggable database pdborcl save state;

If your database version is 12.1.0.1, the above feature is not available, and you need to open the pluggable database each time manually, or create an after startup trigger in the database, for example:

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


"SHOW EVENTS" fails with "Error Code: 1545. Failed to open mysql.event" I have a MariaDB 5.5.56 install (The default CentOS 7 package).

The VM was running on a host that had some problems reaching its storage. This resulted in the OS remounting some filesystems read-only.

The database is a replication slave and was rebuilt by importing a backup from the master. (After a reboot and fsck, which got the root fs and others back to read-write) (There did not appear to be major data corruption, but replication was failing due to duplicate entries, so restoring seemed safer)

mysqldump failed with mysqldump: Couldn't execute 'show events': Failed to open mysql.event (1545).

Events are not currently in use, but are included in the back, in case they are used in the future.

I have tried running REPAIR TABLE mysql.event, however SHOW EVENTS still fails with Error Code: 1545. Failed to open mysql.event.

SELECT * from mysql.event works as on the master and returns no rows.

On the master (unaffected by the storage issue and running an older, MySQL, version, SHOW EVENTS works as expected and returns no results)

After restarting MariaDB, SHOW TABLES now fails with Error Code: 1577. Cannot proceed because system tables used by Event Scheduler were found damaged at server start. SELECT * from mysql.event still works, returning 0 rows.

mysqlcheck --all-databases --check-upgrade -p returns "OK" for all tables, including mysql.event.

Since mysql.event is empty you can use one of the following two options:
1- use --skip-events to skip events from backup.
2- try to recreate the table (firs you need to drop it):

DROP TABLE IF EXISTS `mysql`.`event`;

if you are using MySQL 5.7

CREATE TABLE `mysql`.`event` (
  `db` char(64) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL DEFAULT '',
  `name` char(64) NOT NULL DEFAULT '',
  `body` longblob NOT NULL,
  `definer` char(93) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL DEFAULT '',
  `execute_at` datetime DEFAULT NULL,
  `interval_value` int(11) DEFAULT NULL,
  `interval_field` enum('YEAR','QUARTER','MONTH','DAY','HOUR','MINUTE','WEEK','SECOND','MICROSECOND','YEAR_MONTH','DAY_HOUR','DAY_MINUTE','DAY_SECOND','HOUR_MINUTE','HOUR_SECOND','MINUTE_SECOND','DAY_MICROSECOND','HOUR_MICROSECOND','MINUTE_MICROSECOND','SECOND_MICROSECOND') DEFAULT NULL,
  `created` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `modified` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
  `last_executed` datetime DEFAULT NULL,
  `starts` datetime DEFAULT NULL,
  `ends` datetime DEFAULT NULL,
  `status` enum('ENABLED','DISABLED','SLAVESIDE_DISABLED') NOT NULL DEFAULT 'ENABLED',
  `on_completion` enum('DROP','PRESERVE') NOT NULL DEFAULT 'DROP',
  `sql_mode` set('REAL_AS_FLOAT','PIPES_AS_CONCAT','ANSI_QUOTES','IGNORE_SPACE','NOT_USED','ONLY_FULL_GROUP_BY','NO_UNSIGNED_SUBTRACTION','NO_DIR_IN_CREATE','POSTGRESQL','ORACLE','MSSQL','DB2','MAXDB','NO_KEY_OPTIONS','NO_TABLE_OPTIONS','NO_FIELD_OPTIONS','MYSQL323','MYSQL40','ANSI','NO_AUTO_VALUE_ON_ZERO','NO_BACKSLASH_ESCAPES','STRICT_TRANS_TABLES','STRICT_ALL_TABLES','NO_ZERO_IN_DATE','NO_ZERO_DATE','INVALID_DATES','ERROR_FOR_DIVISION_BY_ZERO','TRADITIONAL','NO_AUTO_CREATE_USER','HIGH_NOT_PRECEDENCE','NO_ENGINE_SUBSTITUTION','PAD_CHAR_TO_FULL_LENGTH') NOT NULL DEFAULT '',
  `comment` char(64) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL DEFAULT '',
  `originator` int(10) unsigned NOT NULL,
  `time_zone` char(64) CHARACTER SET latin1 NOT NULL DEFAULT 'SYSTEM',
  `character_set_client` char(32) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL,
  `collation_connection` char(32) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL,
  `db_collation` char(32) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL,
  `body_utf8` longblob,
  PRIMARY KEY (`db`,`name`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='Events';

MySQL 5.1

Compared to MySQL 5.7, the definer field is smaller.

CREATE TABLE `mysql`.`event` (
  `db` char(64) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL DEFAULT '',
  `name` char(64) NOT NULL DEFAULT '',
  `body` longblob NOT NULL,
  `definer` char(77) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL DEFAULT '',
  `execute_at` datetime DEFAULT NULL,
  `interval_value` int(11) DEFAULT NULL,
  `interval_field` enum('YEAR','QUARTER','MONTH','DAY','HOUR','MINUTE','WEEK','SECOND','MICROSECOND','YEAR_MONTH','DAY_HOUR','DAY_MINUTE','DAY_SECOND','HOUR_MINUTE','HOUR_SECOND','MINUTE_SECOND','DAY_MICROSECOND','HOUR_MICROSECOND','MINUTE_MICROSECOND','SECOND_MICROSECOND') DEFAULT NULL,
  `created` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `modified` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
  `last_executed` datetime DEFAULT NULL,
  `starts` datetime DEFAULT NULL,
  `ends` datetime DEFAULT NULL,
  `status` enum('ENABLED','DISABLED','SLAVESIDE_DISABLED') NOT NULL DEFAULT 'ENABLED',
  `on_completion` enum('DROP','PRESERVE') NOT NULL DEFAULT 'DROP',
  `sql_mode` set('REAL_AS_FLOAT','PIPES_AS_CONCAT','ANSI_QUOTES','IGNORE_SPACE','NOT_USED','ONLY_FULL_GROUP_BY','NO_UNSIGNED_SUBTRACTION','NO_DIR_IN_CREATE','POSTGRESQL','ORACLE','MSSQL','DB2','MAXDB','NO_KEY_OPTIONS','NO_TABLE_OPTIONS','NO_FIELD_OPTIONS','MYSQL323','MYSQL40','ANSI','NO_AUTO_VALUE_ON_ZERO','NO_BACKSLASH_ESCAPES','STRICT_TRANS_TABLES','STRICT_ALL_TABLES','NO_ZERO_IN_DATE','NO_ZERO_DATE','INVALID_DATES','ERROR_FOR_DIVISION_BY_ZERO','TRADITIONAL','NO_AUTO_CREATE_USER','HIGH_NOT_PRECEDENCE','NO_ENGINE_SUBSTITUTION','PAD_CHAR_TO_FULL_LENGTH') NOT NULL DEFAULT '',
  `comment` char(64) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL DEFAULT '',
  `originator` int(10) unsigned NOT NULL,
  `time_zone` char(64) CHARACTER SET latin1 NOT NULL DEFAULT 'SYSTEM',
  `character_set_client` char(32) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL,
  `collation_connection` char(32) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL,
  `db_collation` char(32) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL,
  `body_utf8` longblob,
  PRIMARY KEY (`db`,`name`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='Events';

MariaDB 5.5

Compared to MySQL 5.1 the NOT_USED under sql_mode changes to IGNORE_BAD_TABLE_OPTIONS.

CREATE TABLE `mysql`.`event` (
  `db` char(64) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL DEFAULT '',
  `name` char(64) NOT NULL DEFAULT '',
  `body` longblob NOT NULL,
  `definer` char(77) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL DEFAULT '',
  `execute_at` datetime DEFAULT NULL,
  `interval_value` int(11) DEFAULT NULL,
  `interval_field` enum('YEAR','QUARTER','MONTH','DAY','HOUR','MINUTE','WEEK','SECOND','MICROSECOND','YEAR_MONTH','DAY_HOUR','DAY_MINUTE','DAY_SECOND','HOUR_MINUTE','HOUR_SECOND','MINUTE_SECOND','DAY_MICROSECOND','HOUR_MICROSECOND','MINUTE_MICROSECOND','SECOND_MICROSECOND') DEFAULT NULL,
  `created` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `modified` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
  `last_executed` datetime DEFAULT NULL,
  `starts` datetime DEFAULT NULL,
  `ends` datetime DEFAULT NULL,
  `status` enum('ENABLED','DISABLED','SLAVESIDE_DISABLED') NOT NULL DEFAULT 'ENABLED',
  `on_completion` enum('DROP','PRESERVE') NOT NULL DEFAULT 'DROP',
  `sql_mode` set('REAL_AS_FLOAT','PIPES_AS_CONCAT','ANSI_QUOTES','IGNORE_SPACE','IGNORE_BAD_TABLE_OPTIONS','ONLY_FULL_GROUP_BY','NO_UNSIGNED_SUBTRACTION','NO_DIR_IN_CREATE','POSTGRESQL','ORACLE','MSSQL','DB2','MAXDB','NO_KEY_OPTIONS','NO_TABLE_OPTIONS','NO_FIELD_OPTIONS','MYSQL323','MYSQL40','ANSI','NO_AUTO_VALUE_ON_ZERO','NO_BACKSLASH_ESCAPES','STRICT_TRANS_TABLES','STRICT_ALL_TABLES','NO_ZERO_IN_DATE','NO_ZERO_DATE','INVALID_DATES','ERROR_FOR_DIVISION_BY_ZERO','TRADITIONAL','NO_AUTO_CREATE_USER','HIGH_NOT_PRECEDENCE','NO_ENGINE_SUBSTITUTION','PAD_CHAR_TO_FULL_LENGTH') NOT NULL DEFAULT '',
  `comment` char(64) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL DEFAULT '',
  `originator` int(10) unsigned NOT NULL,
  `time_zone` char(64) CHARACTER SET latin1 NOT NULL DEFAULT 'SYSTEM',
  `character_set_client` char(32) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL,
  `collation_connection` char(32) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL,
  `db_collation` char(32) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL,
  `body_utf8` longblob,
  PRIMARY KEY (`db`,`name`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='Events';


How do I solve an error code 127 when doing a pg_dumpall through a cronjob? I have this simple script to backup all my databases in postgres

    pg_dumpall -U pg_user > /tmp/tmp.txt
    DUMP_STATUS=$?
    echo "Dump status: $DUMP_STATUS" > /tmp/status.txt

The dump status is 0 and the tmp.txt file is properly created when the script is executed from the command line.

However, if I execute this script as a cronjob in my crontab, my tmp.txt is empty (the dump of all databases failed) and my status.txt file contains a dump status 127.

A strange behavior I noticed is that pg_dumpall will pipe the information into the file but also print it on the terminal.  I'm thinking this behavior might be the cause of the problem.

Any idea how I can solve this issue?

I am running FreeBSD:

    > uname -mrs
    FreeBSD 9.1-PRERELEASE amd64

    > psql -V
    psql (PostgreSQL) 9.1.7
    contains support for command-line editing

If the script can work apart from a crontab, you should make sure of the following:

The script file contains the necessary environmental variables

$PGHOME
$PGDATA
$PGPORT
$PATH

Output the standard error to a file for future analysis


Question about "RMAN-06169:" and Oracle When i typed the command "backup database" it mentioned

"RMAN-06169: could not read file header for datafile 5 error reason 4..."etc

and then i checked the STATUS of the datafile 5

(look at the picture)

so i tried to recover ,it didn't work.

it told me that "ORA-00283: recovery session canceled due to errors
ORA-01110: data file 5: '/u01/app/oracle/oradata/abc/abc.dbf'
ORA-01157: cannot identify/lock data file 5 - see DBWR trace file
ORA-01110: data file 5: '/u01/app/oracle/oradata/abc/abc.dbf'
(datafile 7 is the same situation.)"

It reminded me that i once delected the document-abc under the graphic interface,which meant that i didn't use the command to delect the file.

so i tried to alter the datafile 5 and datafile 7..  it didn't work also.

how to alter the datafile 5 and 7? and backup database?
thanks very much~！

Try this command to SKIP the offline and corrupted datafiles:

BACKUP DATABASE
    SKIP INACCESSIBLE
    SKIP OFFLINE;

  SKIP INACCESSIBLE: Inaccessible datafiles. A datafile is only considered inaccessible if it cannot be read. Some offline datafiles can still be read because they exist on disk. Others have been deleted or moved and so cannot be
  read, making them inaccessible.

  SKIP OFFLINE: skipping Offline datafiles;

Details: https://docs.oracle.com/cd/B12037_01/server.101/b10734/rcmbackp.htm


Should I store relation counts as their own columns, or will indexes be fast enough? Let's say I have this schema:
posts
- id
- body

likes
- id
- post_id

Should I add a likes integer column to posts which increments every time a new record is inserted into likes?
Or will a join still be fast once it reaches millions of rows?

Several comments:

We can't guess what's fast enough for you. As the comment above says, it depends on the speed of your hardware, but it also depends on how well your data fits in RAM, it depends on how frequently the query is run, it depends on how much data exists, it depends on how fast you need it to be.
Your app might need to denormalize or cache this value, but we don't know if your app operates at a scale where it needs this yet. Be conservative with such optimizations. They might be needed, but if they are not needed, it just adds complexity to your app for no reason.

You don't need a join to count rows in the likes table for a given post, as @nbk points out. Probably you do need to use a join if you want to count the likes for many posts, but we can't guess if you need to do that.

Given the attributes of the likes table you've shown, why do you even need this table? You don't record the person who added each like, so you can't implement a feature to let users reverse their like. You also don't record any other attributes, such as the datetime of the like, or the type of like (e.g. facebook's like, heart, care, wow, sad, angry). Why bother having the table?

If you wonder if it will still be fast enough once it reaches millions of rows, then test it with millions of rows. This is part of your job as a software developers: come up with a way to test things, don't guess or expect someone else to tell you the right answer. You can load a table with millions of random rows for likes, and then try out your query. This will be more accurate than a Stack Overflow answer, because it will be running on your server's hardware. We don't have to make assumptions about the speed or capacity of your environment.


Check (health) status of MySQL schema table I have a large MySQL database which has around 1,500 tables and the file of the database is around 30GB. I would like to suggest me some ways to monitor the health of my database in overall and how i can check the health status of mysql schema table.

How can I monitor the health of my database in overall? I use mysql workbence, but since i am new to this, i don't know what to check!

To monitor your database's health in overall you could simply use:

mysqlcheck --all-databases --auto-repair

or on weekly basis (if you prefer, due to optimize option resource usage)

mysqlcheck --all-databases --auto-repair --optimize

To verify tables:

mysql
\u mydb
CHECK TABLE mytable FAST QUICK;

The first command will check the integrity for all tables of all databases, if there's any corruption your table will be repaired automatically.
And the second will additionally optimize the table on disk.

Check the explanation about it here:
What is the difference between optimize table and analyze table in mysql


unique constraint violated I have code create table

CREATE TABLE OFPHONE
(
  PHONEID     INTEGER                           NOT NULL,
  HOTLINE     VARCHAR2(20 CHAR),
  EXT         VARCHAR2(20 CHAR),
  PHONE       VARCHAR2(30 CHAR),
  PASSWORD    VARCHAR2(30 BYTE),
  NAME        VARCHAR2(30 BYTE),
  CREATEDATE  TIMESTAMP(6)                      DEFAULT CURRENT_TIMESTAMP
)
TABLESPACE USERS
PCTUSED    0
PCTFREE    10
INITRANS   1
MAXTRANS   255
STORAGE    (
            INITIAL          64K
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
           )
LOGGING
NOCOMPRESS
NOCACHE
NOPARALLEL
MONITORING
ENABLE ROW MOVEMENT;

COMMENT ON COLUMN OFPHONE.PASSWORD IS 'Password';

CREATE UNIQUE INDEX OFPHONE_PK ON OFPHONE
(PHONEID)
LOGGING
TABLESPACE USERS
PCTFREE    10
INITRANS   2
MAXTRANS   255
STORAGE    (
            INITIAL          64K
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
           )
NOPARALLEL;
CREATE OR REPLACE TRIGGER TRIGGER_OFPHONE_AFTER_INSERT2
AFTER INSERT
ON OFPHONE
REFERENCING NEW AS NEW OLD AS OLD
FOR EACH ROW
declare
    tmp varchar2(20);

begin

    insert into ofphone_tmp (PHONEID,HOTLINE,EXT,PHONE,PASSWORD,NAME,CREATEDATE)
    values (:new.PHONEID,:new.HOTLINE,:new.EXT,:new.PHONE,:new.PASSWORD,:new.NAME, sysdate);

    insert into OFUSER(USERNAME,PLAINPASSWORD,NAME,EMAIL,CREATIONDATE,MODIFICATIONDATE)
    values(:NEW.PHONE,:NEW.PASSWORD,:NEW.NAME,trim(' ' from :NEW.NAME)||'@localhost',sysdate,sysdate);

    --commit;

   for x in
   (select  phone,name,hotline from ofphone_tmp where hotline = :NEW.hotline and phone <> :new.phone) loop

        insert into ofRoster values (SEQ_OFROSTER.nextval,:new.PHONE,x.phone||'@localhost',3,-1,-1,X.name);

        insert into ofRoster values (SEQ_OFROSTER.nextval,x.phone,:new.PHONE||'@localhost',3,-1,-1,:new.name);

   end loop;

end;
/

I tried debug code snippet

But I checked the details of the value of the block
for cur in (has data)
and select SEQ_OFPHONE (has data)
are valid.

Please tell me this error case.

begin

    for cur in(select
                c.COMPANY_MOBILE,
                e.EMPLOYEE_NAME,
                E.MOBILE_PHONE,
                E.EMPLOYEE_NO,
                E.EMPLOYEE_PWD,
                E.Email
             from TAB_XN_EMPLOYEE e
            inner join TAB_XN_COMPANY c
            on e.COMPANY_ID = c.COMPANY_ID
            and c.COMPANY_MOBILE  = '0912400604'
            ) loop
            insert into ofphone( PHONEID,HOTLINE,EXT,PHONE,PASSWORD,NAME,CREATEDATE)
            values(SEQ_OFPHONE.nextval,cur.COMPANY_MOBILE,cur.EMPLOYEE_NO,
            cur.MOBILE_PHONE,cur.EMPLOYEE_PWD,cur.EMPLOYEE_NAME,sysdate);

    end loop;

end;

Error

  ORA-00001: unique constraint (MEG.OFUSER_PK) violated ORA-06512: at
  "MEG.TRIGGER_OFPHONE_AFTER_INSERT2", line 20 ORA-04088: error during
  execution of trigger 'MEG.TRIGGER_OFPHONE_AFTER_INSERT2' ORA-06512: at
  line 15

Given your error ("OFUSER_PK"), this is an indication that the primary key on the OFUSER table is the problem. Check your insert statements for OFUSER - something seems wonky there.


Why does sys.fn_hadr_backup_is_preferred_replica take so long? Is there a better way? I am using sys.fn_hadr_backup_is_preferred_replica to query the databases on each of my Availability Group replicas to figure out if the database should be backed up on that replica - then, if the function returns true, checking msdb.dbo.backupset to see if the database has been backed up recently.

The problem I'm experiencing is that the function takes a long time to run (about 3 seconds per database) - can anyone recommend an alternative that is faster?

In response to the comment below, I am currently on SQL 2012 SP1 (11.0.3000.0) - however, the queries I am creating will need to target multiple different installations of SQL Server 2012 on multiple customer sites, so, even if the performance of this system function has been improved in subsequent updates, I may not be able to force people to update, unfortunately.

The problem I'm experiencing is that the function takes a long time to run (about 3 seconds per database)

It's possible that this is among a growing list of dynamic management objects that don't support predicate push-down, so you will probably notice higher delays as the number of AGs and/or AG databases increases on any given replica.

And it's possible that's exactly what was fixed, as @Kin pointed out, in Connect #783011; sadly the Connect item doesn't indicate where exactly you can find the fix.

Based on some quick searches (and the date of the comment), I think you will want at least 2012 SP1 CU8, which (via KB #2918791) fixed a regression introduced in 2012 SP1 CU7 (via KB #2887115).

I don't see any evidence in later builds that suggest this function has been modified after 2012 SP1 CU8, and the modification is possibly related to the regression that was introduced.

But I am a huge proponent of using the most recent service pack branch:

SP2 is the only currently supported branch; RTM and SP1 have been retired, so if you're still on those, it's time to move;
the latest CU (currently that is SP2 CU #8);
if your applications use connection pooling, you may also be interested in this on-demand hotfix.

  can anyone recommend an alternative that is faster?

If you look at the source for sys.fn_hadr_backup_is_preferred_replica, you can see that there is some convoluted logic and waterfall variable population that you could probably short circuit if you are not using mirroring or log shipping, by writing your own, cleaned-up version. But then you get to calls to forbidden system functions like RetrieveDbReplicaState(), which you can't call yourself, and you realize that there isn't a way to rewrite this function and replicate its full functionality

So, your best bet is to apply whatever fix Microsoft has supplied in order to optimize the function. If you still experience slowness after moving to the latest build, you can file another Connect item, or you can raise a case with CSS directly (just be aware that if you have 10,000 databases in your AG, they may just tell you to stop doing that).


Does a login need to be created on MSSQL Always On replica before being granted roles on the primary? Consider a SQL Server Login MyLogin that exists on PrimaryServer but not SecondaryServer in an Always-On Availability group. MyLogin has a number of roles already assigned.

If the login MyLogin is later created on SecondaryServer using the hashed password and SID from PrimaryServer, will it automatically have the same roles on the database mirrored on SecondaryServer?

Does it matter that the permissions were granted on the database on the PrimaryServer before the login was created on SecondaryServer?

It doesn't matter where the login is created first.  The only important thing is that the SIDs match.  All permissions, role memberships, etc. that are assigned to the associated users on databases in the availability group on the primary will be effective on the secondaries for the login with the same SID.


Which hard disks for PostgreSQL? The size of the our PostgreSQL data directory is 100 GByte.

Up to now we use traditional hard disk drives and hardware RAID 10.

Which hard disk drives and RAID setup would give us maximum performance?

The budget is at maximum 5000$.

The load is like in most average databases. Maybe 1/5 writing (update or inserts) 4/5 reading (select).

That is a big fat "it depends on your application(s)". You say how large the total data is but not the commonly active data-set? All in one DB or several? If several is it the same structure in each (i.e. multi-tenant with one tenant per DB) or a mix of different application databases? What sort of read and write IO loads do the database(s) see and what level of concurrent requests. How much memory on the database server? If it is very read heavy and the data is small enough to fit in your RAM then disk choice is not going to make a lot of difference.

There are a few very general rules for databases:

SSD will trump traditional disks by a fair margin unless your data is large enough for that to be far too expensive. If your data is too large for SSDs to be affordable perhaps use partitioning (either manual by separating tables and/or databases between different drives and/or "real" partitioning to spread the data more granularly) to keep active data on the faster drives and archive data elsewhere.
As in the last point: separating data between different drives/arrays can help performance greatly in some cases even if the drives are all of the same performance levels, by reducing IO contention.
RAID for drive error protection and speed: RAID10 is generally recommended. R5 and R6 can have a performance issue for workloads with any significant write activity. Sometimes four drives are used as two independent RAID1 arrays instead of one RAID10 (two separate mirrored volumes instead of one striped set of two mirrors) to separate heavy IO onto specific disk sets - for some workloads this can be advantageous but it is more work to manage.
Have as much RAM as you can throw at it: if your data all fits in memory that'll be as fast as it can go.


MySQL Dumper creating multiple files - spamming email confirmations It's been a little while since I ran mysql dumper, but have done so successfully in the past many times, however when trying now something weird is happening.

I had not changed the settings (I have now after the problem reset to default to try and fix) but now when I run it I'm getting multiple files generated often 1 second after the other as can be seen in this screenshot of the files on the ftp;

I had email confirmation turned on too, and I'm pretty much getting an email sent every second as the interface refreshes.

The interface is showing something like this;

Normally, the last bit (2 seconds, 0 page refreshes) might show something like 40 seconds, 25 page refreshes as it processes bigger databases.

The version of PHP running on the server was updated a little while ago from 5.3 to 5.6.18. Could that have anything to do with this odd behaviour? As I said, the settings had not been changed - even so the default settings are pretty much what I had used anyway.

EDIT

I have also just tried re-downloading mysqldumper from scratch and installing it. Unfortunately, nothing changed. There must be some kind of configuration issue, but I don't know where to find that out...

These are my current settings - they are largely unchanged from the default;

If i click auto detect here I get the value 120795955

Do not OPTIMIZE tables before backups.  That consumes a lot of time, thereby making there by more 1-minute dumps.  And OPTIMIZE rarely does anything useful.

You have "max file size" = 0 KB -- does that mean unlimited, or small?  I see that the files tend to be a few hundred KB.


Connectivity timeout between cluster nodes I lose communication between my clusters almost 1 am to 1.30 am and it shows errors like 35206 on primary, 976 on clusters, sometimes the cluster is removed due to lost heartbeat leading to 1135 error , 1069 error. I have a strong feeling that my connection breaks everyday at that time but im clueless where to start kind of lost. any help will be greatly appreciated.

Have you checked SQL error logs? Start with what the description of errors. Is it because it lost quorum? Is there high CPU in any of the nodes? Are you on FCI or AAG?

We had to adjust timeout between our replicas when the databases were reporting unhealthy during high peak ETL loads.

0
down vote
Set up trace to investogate what other things are running on the server. Then check SQL wait stats. https://www.sqlskills.com/blogs/paul/wait-statistics-or-please-tell-me-where-it-hurts/

If there is no load - increase your timeout to a higher value (https://blogs.msdn.microsoft.com/alwaysonpro/2014/06/03/connection-timeouts-in-multi-subnet-availability-group/)) and see if the errors go away. However, if you have no problems in your workload then you should not worry about these errors. First you need to assess if users are complaining of slowness, are there any jobs failing during this time? You have to ask these questions before trying to simply reposlve an error.


Altering View in MS SQL Server by Adding Col based on Join I have created a view that queries a table called Sheet1$. It has a carID field, and a carName field.

So I created my view by doing the following:

CREATE VIEW corvettes
AS
SELECT *
FROM Sheet1$
WHERE carName = 'corvette'

I have another table called Sheet2. It has a carID field, and a carYear field. I want to alter my original view by adding a COL to it called YEAR and then populate YEAR with the values of Sheet2$.carYear via join of the two carID fields.

I can get the YEAR field appended with the correct data, but if there is no match of the ID's, then the whole row goes away. I'd rather have the YEAR say NULL instead of going away if the ID's don't match.

This is what I have. Where am I going wrong?

ALTER VIEW corvettes
AS
SELECT a.carID, a.carName, b.carYear
FROM Sheet1$ a, Sheet2$ b
WHERE a.carID = b.carID
AND carName = 'corvette'

As far as some cars has not Year, you should use a LEFT JOIN on this way:

ALTER VIEW corvettes
AS
SELECT    a.carID, a.carName, b.carYear
FROM      Sheet1$ a
LEFT JOIN Sheet2$ b
ON        a.carID = b.carID
WHERE     a.carName = 'corvette'

Have a look at Using OUTER JOINS at Microsoft Docs.


multi-tenant data related to multiple customer? tenant type design on the database schema tables. We have about 20 customers data and single web application is serving it.

Question, how do we improve query performance or what type of database architecture would you recommend in this situation?

In ideal world it would be each customer sits on different database user in that way we have separation of data as well as performance. But if we can't then what are the options??

Customers come and go.  Sometimes they have the right to take their data with them.  To extract a subset of data from one schema where the data could be anywhere and the only marker might be a tenant number is a lot harder than putting each customer's data in one schema.

One solution I have seen used is to have a master schema with the metadata concerning the unit of work that is relevant to your application.  So if your application sells something you have tables that list the types of sales, types of sales terms, lists of provinces, states, countries.

Then in each customers schema you have child tables which can extend the parent types with additional columns.  These tables have a primary key which is foreign keyed to the master tables.

If your ORM solution supports inheritance this can work quite well.  Reporting gets a bit tedious as you have additional joins to the master tables.


Can user variables be assigned of BIT datatype? Based on the documentation:

  User variables can be assigned a value from a limited set of data types: integer, decimal, floating-point, binary or nonbinary string, or NULL value.

As you see, user variables aren't supported BIT datatype. Right? Ok, I have this code:

BEGIN
    SELECT active INTO @active FROM users WHERE id = new.user_id;
    IF (IFNULL((@active & b'100' > 0), 0) < 1) THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = "You cannot vote";
    END IF;
END

Note: active column is BIT(10).

You know, my code surprisingly works as well .. Why? I mean how MySQL treats with @active variable as a BIT datatype?

b'100' is just the number 4 represented in a different way.  Similarly for the column active and the variable @active.


PostgreSQL: interpolate missing value I have a table in PostgreSQL with a timestamp and a value.
I would like to interpolate the missing values under "lat".
The value under "lat" are tidalheights above a datum.
For the purpose it is ok to interpolate the missing value linear between the two known values.
What is the best method to do so in PostgreSQL?

Edit 20200825
I have solved this problem in a different way using the QGIS fieldcalculator. Problem with this method: it takes a long time and it the process runs client-side and I would like to run it directly on the server.
In steps, my workflow was:

The interval between the recorded "lat" values is 10 minutes. I calculated the increment per minute between two recorded values and stored this in an extra column called "tidal_step" at the recorded "lat"-value.  (i stored the timestamp also as an "epoch" in a column)

In QGIS:
tidal_step =
-- the lat value @ the epoch, 10 minutes or 600000 miliseconds from the current epoch:
(attribute(get_feature('werkset','epoch',("epoch"+'600000')),'lat') -
-- the lat value @ the current
attribute(get_feature('werkset','epoch',"epoch"),'lat'))
/10

for the first two values from the example image that results in:
(4.95 - 5.07) /10 = -0.012

I determined the amount of minutes of the "lat"-value to be interpolated, past the last recorded instance where a "lat" value was recorded and stored this in a column: "min_past_rec"

In QGIS:
left(
right("timestamp",8) --this takes the timestamp and goes 8 charakters from the right
,1) -- this takes the string from the previous right( and goes 1 character left

for the first value in the example:
2019-01-01 00:15:15 returns: '5'
This is 5 minutes past the last recorded value.

I interpolated the missing values by adding the ("min_past_rec" * "tidal_step") to the last recorded "lat" value and stored this in in column called "lat_interpolated"

In QGIS
CASE
WHEN "lat" = NULL
THEN
-- minutes pas the last recorded instance:
("min_past_rec" *
-- the "tidal_step" at the last recorded "lat"-value:
(attribute(get_feature('werkset','epoch',
("epoch" - --the epoch of the "lat" value to be interpolated minus:

left(right("timestamp",8),1) * 600000 -- = the amount of minutes after the last recorded instance.
+ left(right("timestamp",6),2) * 1000) --  and the amount of seconds after the last recorded instance.
),'tidal_step')) +

-- the last recorded "lat"-value

(attribute(get_feature('werkset','epoch',("epoch" - left(right("timestamp",8),1) * 600000 + left(right("timestamp",6),2) * 1000)),'lat'))

With data from the example:
2019-01-01 00:17:33:
"lat_interpolated" = "min_past_rec" * "tidal_step" + "lat" =
7*-0.012 + 4.95 = 4.866

delete obsolete columns from database

Which statements/script should I use in PostgreSQL to perform the same task?

I have a (partial) solution - what I did was the following (see the fiddle available here):
The algorithm I used for interpolating was

if there's a sequence of 1 NULL, take the average of the value above and the value below.

A sequence of 2 NULLs, the top assigned value is the average of the two records above it and the bottom assigned one is the average of the two records below.

In order to do this, I did the following:
Create a table:
CREATE TABLE data
(
  s SERIAL PRIMARY KEY,
  t TIMESTAMP,
  lat NUMERIC
);

Populate it with some sample data:
INSERT INTO data (t, lat)
VALUES
('2019-01-01 00:00:00', 5.07),
('2019-01-01 01:00:00', 4.60),
('2019-01-01 02:00:00', NULL),
('2019-01-01 03:00:00', NULL),
('2019-01-01 04:00:00', 4.7),
('2019-01-01 05:00:00', 4.20),
('2019-01-01 06:00:00', NULL),
('2019-01-01 07:00:00', 4.98),
('2019-01-01 08:00:00', 4.50);

Note that records 3 & 4 and 7 are NULL.
And then I ran my first query:
WITH cte1 AS
(
  SELECT d1.s,
    d1.t AS t1, d1.lat AS l1,
    LAG(d1.lat, 2)  OVER (ORDER BY t ASC) AS lag_t1_2,
    LAG(d1.lat, 1)  OVER (ORDER BY t ASC) AS lag_t1,
    LEAD(d1.lat, 1) OVER (ORDER BY t ASC) AS lead_t1,
    LEAD(d1.lat, 2) OVER (ORDER BY t ASC) AS lead_t1_2
  FROM data d1
),
cte2 AS
(
  SELECT
    d2.t AS t2, d2.lat AS l2,
    LAG(d2.lat, 1) OVER(ORDER BY t DESC) AS lag_t2,
    LEAD(d2.lat, 1) OVER(ORDER BY t DESC) AS lead_t2
  FROM data d2
),
cte3 AS
(
  SELECT t1.s,
    t1.t1,  t1.lag_t1_2, t1.lag_t1, t2.lag_t2, t1.l1, t2.l2,
    t1.lead_t1, t2.lead_t2, t1.lead_t1_2
  FROM cte1 t1
  JOIN cte2 t2
  ON t1.t1 = t2.t2
)
SELECT * FROM cte3;

Result (spaces mean NULL - it's much clearer on the fiddle):
s   t1  lag_t1_2    lag_t1  lag_t2  l1  l2  lead_t1     lead_t2     lead_t1_2
1   2019-01-01 00:00:00             4.60    5.07    5.07    4.60
2   2019-01-01 01:00:00         5.07        4.60    4.60        5.07
3   2019-01-01 02:00:00     5.07    4.60                    4.60    4.7
4   2019-01-01 03:00:00     4.60        4.7             4.7         4.20
5   2019-01-01 04:00:00             4.20    4.7     4.7     4.20
6   2019-01-01 05:00:00         4.7         4.20    4.20        4.7     4.98
7   2019-01-01 06:00:00     4.7     4.20    4.98            4.98    4.20    4.50
8   2019-01-01 07:00:00     4.20        4.50    4.98    4.98    4.50
9   2019-01-01 08:00:00         4.98        4.50    4.50        4.98

Note the use of the LAG() and LEAD() Window functions (documentation). I've used them on the same table, but sorted differently.
This and using the OFFSET option means that from my original single lat column, I now have 6 extra columns of "generated" data which are very useful for assigning values to the missing NULL values. The last (partial) piece of the puzzle is shown below (full SQL query is at the bottom of this post and also in the fiddle).
cte4 AS
(
  SELECT t1.s,
  t1.l1 AS lat,
    CASE
      WHEN (t1.l1 IS NOT NULL) THEN t1.l1
      WHEN (t1.l1 IS NULL) AND (t1.l2) IS NULL AND (t1.lag_t1 IS NOT NULL)
        AND (t1.lag_t2 IS NOT NULL) THEN ROUND((t1.lag_t1 + t1.lag_t2)/2, 2)
      WHEN (t1.lag_t2 IS NULL) AND (t1.l1 IS NULL) AND (t1.l2 IS NULL)
        AND (t1.lead_t1 IS NULL) THEN ROUND((t1.lag_t1 + t1.lag_t1_2)/2, 2)
      WHEN (t1.l1 IS NULL) AND (t1.l2 IS NULL)  AND (t1.lag_t1 IS NULL)
        AND (t1.lead_t2 IS NULL) THEN ROUND((t1.lead_t1 + t1.lead_t1_2)/2, 2)
      ELSE 0
    END AS final_val
  FROM cte3 t1
)
SELECT s, lat, final_val FROM cte4;

Final result:
s    lat    final_val
1   5.07         5.07
2   4.60         4.60
3   NULL         4.84
4   NULL         4.45
5   4.7           4.7
6   4.20         4.20
7   NULL         4.59
8   4.98         4.98
9   4.50         4.50

So, you can see that the calculated value for record 7 is the average of records 6 & 8 and record 3 is the average of records 1 & 2 and the assigned value for record 4 is the average of 5 & 6. This was enabled by the use of the OFFSET option for the LAG() and LEAD() functions. If you get  sequences of 3 NULLs, then you'll have to use an OFFSET of 3 and so on.
I'm not really happy with this solution - it involves hard-coding for the number of NULLs and those CASE statements will become even more complex and horrible. Ideally some sort of RECURSIVE CTE solution is required, but I  HTH!
===============================  Full Query ========================
WITH cte1 AS
(
  SELECT d1.s,
    d1.t AS t1, d1.lat AS l1,
    LAG(d1.lat, 2)  OVER (ORDER BY t ASC) AS lag_t1_2,
    LAG(d1.lat, 1)  OVER (ORDER BY t ASC) AS lag_t1,
    LEAD(d1.lat, 1) OVER (ORDER BY t ASC) AS lead_t1,
    LEAD(d1.lat, 2) OVER (ORDER BY t ASC) AS lead_t1_2
  FROM data d1
),
cte2 AS
(
  SELECT
    d2.t AS t2, d2.lat AS l2,
    LAG(d2.lat, 1) OVER(ORDER BY t DESC) AS lag_t2,
    LEAD(d2.lat, 1) OVER(ORDER BY t DESC) AS lead_t2
  FROM data d2
),
cte3 AS
(
  SELECT t1.s,
    t1.t1,  t1.lag_t1_2, t1.lag_t1, t2.lag_t2, t1.l1, t2.l2,
    t1.lead_t1, t2.lead_t2, t1.lead_t1_2
  FROM cte1 t1
  JOIN cte2 t2
  ON t1.t1 = t2.t2
),
cte4 AS
(
  SELECT t1.s,
  t1.l1 AS lat,
    CASE
      WHEN (t1.l1 IS NOT NULL) THEN t1.l1
      WHEN (t1.l1 IS NULL) AND (t1.l2) IS NULL AND (t1.lag_t1 IS NOT NULL)
        AND (t1.lag_t2 IS NOT NULL) THEN ROUND((t1.lag_t1 + t1.lag_t2)/2, 2)
      WHEN (t1.lag_t2 IS NULL) AND (t1.l1 IS NULL) AND (t1.l2 IS NULL)
        AND (t1.lead_t1 IS NULL) THEN ROUND((t1.lag_t1 + t1.lag_t1_2)/2, 2)
      WHEN (t1.l1 IS NULL) AND (t1.l2 IS NULL)  AND (t1.lag_t1 IS NULL)
        AND (t1.lead_t2 IS NULL) THEN ROUND((t1.lead_t1 + t1.lead_t1_2)/2, 2)
      ELSE 0
    END AS final_val,
    t1.lead_t1_2
  FROM cte3 t1
)
SELECT s, lat, final_val, lead_t1_2 FROM cte4;


Change current working directory from MySQL client prompt Is there anyway to change the current working directory from the MySQL client prompt?

That would allow me to use relative paths for the tee, source, and another command which I don't recall just now (I just noticed that the post got truncated my copy/pasting, and I had listed a few example commands in the original text file draft -- being new to MySQL, I'm that familiar with the commands off the top of my head).

I found from other apps that changing the working directory is extremely useful in general, e.g., in Vim and Matlab.

You can't. There is no code in the mysql client executable to change directory.


Hash IDs (such as base64 YouTube video Ids): store as int/bigint, or as string, and why? I want to generate "unguessable" IDs, rather than /record/1, /record/2, /record/3 etc.

YouTube's style of using base 64 allows for 16.7 million IDs to be stored in just 4 characters (64 * 64 * 64 * 64).  This would be more than enough for my needs.

My only pondering is whether I should be storing them as ints in the database and converting them every time a request comes in (or when I need to generate a url), or storing them as the string that the user will see.  And why?

A couple of follow-up questions:

1) rather than generating random integers and converting them to the base64 string, should I use auto-increment numbers with a salt to randomise the alphabet, so I can get apparently "random" identifiers from IDs of 1 2 3 4 5 6 7 etc.  And why?

2) for table relationships should I use whatever I've generated as my PK/FK, or should that be a separate  column and just a simple auto-increment int, for some reason?

In SQL Server, I would store this as an int, and use CRYPT_GEN_RANDOM(4) to generate cryptographically random numbers for each row.

Here's an example:

DECLARE @c int;
DECLARE @c_base64 varchar(8);
DECLARE @c_converted int;

--get the cryptographically random integer
SELECT @c = CONVERT(int, crypt_gen_random(4));
--convert it into a base-64 string
DECLARE @c int;
DECLARE @c_base64 varchar(8);
DECLARE @c_converted int;

--get the cryptographically random integer
SELECT @c = CONVERT(int, crypt_gen_random(4));
--convert it into a base-64 string
SELECT @c_base64 = (SELECT CONVERT(varbinary(4), @c) FOR XML PATH(''), BINARY BASE64);
--convert it back to an int
SELECT @c_converted = CONVERT(int, (CONVERT(xml, @c_base64).value('.', 'varbinary(4)')));
--display the original, the string, and the converted string
SELECT original = @c, converted = @c_base64, unconverted = @c_converted;

The results look like:

+-----------+-----------+-------------+
| original  | converted | unconverted |
+-----------+-----------+-------------+
| 629493479 | JYVO5w==  |   629493479 |
+-----------+-----------+-------------+

Why?  Storing integers will consume half the space of storing the converted string.  This may not seem like much on disk, but if you have a table with half a billion rows, every byte counts.  Wasting 2 bytes per row would mean you'd need an extra gigabyte of RAM to scan the table in that scenario.


Database for timeseries data I work in a fintech company and we are redesigning our data system.

We have around 120 GB of compressed data stored in the filesystem.
The data concerns around 80K different timeseries each one of them with around 8K up to 5M rows.
The total estimated number of rows is 8 billion. Each row should hold 6 numeric values, time and an identifier.

The queries we want to run are:

Give me all data for a timeseries between two timestamps
Give me all data for a timeseries in single element buckets of n minutes

Everyday we update around 10K of these timeseries with 8K up to 40K records that concern the data of the last month. That is around 8M rows and takes around 4 hours.

We are looking for something that can handle the daily write load in less than 3 hours while maintaining fast query performance.
Our queries concern 3K timeseries, they scan around 3M rows and return 3.5 K rows for each one of them. This currently happens in 1 minute.

The current system is not so sophisticated, all data is kept in a file server and is synchronized daily to the local computers of the interested parties.
The system worked well for some time and now we are looking for something more robust and scalable.

Our hard requirement is the daily ingestion time but we have some flexibility on how much the reading queries should take.

Can a database give us performance and scalability?
If so what would you choose and why?
What kind of infrastructure do we need to support such a system?

Creating 8M new records should take seconds, not hours.  You just need to do Bulk Loading instead of slow-by-slow loading.  (calling COMMIT after every row would be a "bad idea" as it will limit you to 1row/5ms).

Same concept goes for UPDATE of the data.  (Although. I would expect minutes).  Bulk operations is key.

From my experience,  your main concern will be Disk I/O performance.  You improve this by using RAID systems.  You'll probably have an extra terabyte or 2, but your performance,  and redundancy, should be greatly improved over current setup.

Which RDBMS?

I would go with Oracle.  But, that is because local resources (me) are experts in that technology.  (It also comes with a cool web based application development frame work).

Actually, I recommend you try out each of them with your actual data and pick the one that fits your company the best.


connect to PostgreSQL server: FATAL: no pg_hba.conf entry for host Hello I am trying to run a website sent to me but after doing so this error appeared

connect to PostgreSQL server: FATAL: no pg_hba.conf entry for host "4X.XXX.XX.XXX", user "userXXX", database "dbXXX", SSL off in C:\xampp\htdocs\xmastool\index.php on line 37

after Googling it it says that i just need to add an entry in the pg_hba.conf file for that particular user.
this is my pg_hba.conf file.

# TYPE  DATABASE        USER            ADDRESS                 METHOD
# IPv4 local connections:
local dbXXX userXXX md5
host    dbXXX  userXXX  XX.XXX.XXX.XXX           md5
host    all             all             127.0.0.1/32            md5
# IPv6 local connections:
host    all             all             ::1/128                 md5
# Allow replication connections from localhost, by a user with the
# replication privilege.
#host    replication     postgres        127.0.0.1/32            md5
#host    replication     postgres        ::1/128                 md5

but after doing so, the error still persist. I restarted my XAMPP server several times but no changes appears.
Thanks in advance

Add or edit the following line in your postgresql.conf :

listen_addresses = '*'

Add the following line as the first line of pg_hba.conf. It allows access to all databases for all users with an encrypted password:

# TYPE DATABASE USER CIDR-ADDRESS  METHOD
host  all  all 0.0.0.0/0 md5

Restart Postgresql after adding this with service postgresql restart or the equivalent command for your setup.


SSMS execution time queries besides SET STATISTICS When I run my query, it prints multiple execution times instead of just one. I only want one so what should I use instead of SET STATISTICS?

SET STATISTICS TIME ON
DECLARE @firstNum INT, @secondNum INT, @thirdNum INT, @evenSum INT
SET @firstNum = 1
SET @secondNum = 2
set @thirdNum = 2
SET @evenSum = 2
WHILE (@thirdNum <= 4000000)
BEGIN
    SET @thirdNum = @firstNum + @secondNum
    SET @firstNum = @secondNum
    SET @secondNum = @thirdNum
    IF (@thirdNum % 2) = 0
    SET @evenSum += @thirdNum
END
PRINT 'Answer = ' + CONVERT(VARCHAR, @evenSum)
SET STATISTICS TIME OFF

It prints this like a hundred times:

SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.

 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.

 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.

In the "Query" menu in SSMS there is an option "Include Client Statistics" that you can look at.

There is no TSQL command like SET STATISTICS that prints out cumulative stats per batch.

You could of course just do

DECLARE @StartTime DATETIME2 = SYSUTCDATETIME()

--Your code here

SELECT DATEDIFF(NANOSECOND, @StartTime, SYSUTCDATETIME())

But on my desktop the code runs far too quickly for the resolution of SYSUTCDATETIME() so most of the time it returns 0.

If you just want to know that the code runs "fast enough" to likely not need any performance tuning this may be sufficient.

For a higher resolution option you could get the value of cpu_ticks in sys.dm_os_sys_info before and after execution and try and translate this back to clock time (e.g. by getting the diff and dividing by 3,000 on a 3GHZ processor to get μs) but this may not be reliable.

You could also wrap the code to test into a temporary stored procedure and look at the stats collected for that

CREATE PROC #P AS
BEGIN
YourCodeHere:
END

GO

EXEC #P

SELECT last_execution_time,
       execution_count,
       total_worker_time  AS [total_worker_time μs],
       total_physical_reads,
       total_logical_writes,
       total_logical_reads,
       total_elapsed_time AS [total_worker_time μs]
FROM   tempdb.sys.dm_exec_procedure_stats
WHERE  object_id = OBJECT_ID('tempdb..#P')

But probably the easiest way of getting useful timings for the original code is simply to run it in a loop for a million times and time the overall duration of that - so that any inaccuracies caused by the resolution of SYSUTCDATETIME() average out to become negligible.


Compression not available on SQL Server Standard? Options? So as they say, everyday is a school day. Today I learned that my workplace, runs SQL Server Standard edition, where I would have assumed Enterprise was in place. Although in reality shouldn't be surprised!

For some context, we have a very large database that houses our warehouse data. As the database has grown to a large size, it's causing issues with space on the server along with some application performance. So looking at it from my perspective I suggested we archive and purge the PROD database, to house only 18 months data in the PROD environment.

Wrote my scripts and tested them and all fine. I then went to compress the tables I had deleted data from, to find error messages that compression is not available in SQL Server Standard and requires Enterprise edition.

Wondering what my next steps are here? My assumption is that even though I am deleting a lot of data, we won't actually benefit in terms of performance, and space requisition until the tables get compressed.

Shrinking is something I guess I've always shy'd away from, many articles or posts here would advise not to use it.

Wondering, what sort of options do I have here?

Is my assumption correct, in that without compressing, we won't regain space from the trimmed database?

Since 2016sp1 all the compression features have been available in all editions, though that may not help you at all as upgrading your production environments (I'm assuming they run 2014 or earlier?) isn't something to just do on a whim.

  I then went to compress the tables I had deleted data from

If you are deleting the data is compression really what you need? If it is, then to suggest how you might reduce the size of the remaining data we'd need to know a lot more about that data and its use.

  Shrinking is something I guess I've always shy'd away from,

Shrinking is usually not what you want to do so you are correct to be cautious - if the data could grow back to use the space again then you might as well leave it allocated to the DB as this will avoid the future (potentially performance harming if they happen at an inconvenient time) growth operations, and the shrink process can cause significant fragmentation especially if (as I've seen done) the data files are shrunk on a regular basis.

But if you have reduced the data size of the database by archiving/deleting a large amount of data and you don't expect the amount of data to grow back to close to its original size any time soon, a one-off shrink should not have significantly concerning side-effects. But shrink to how large the data is expected to be after a reasonable length of time, don't shrink down to the smallest the current data will fit in.


upgrade from Mariadb 10.2.* to 10.3.* using mariabackup? Anyone successfully done upgrade from 10.2 to 10.3 using mariabackup without any downtime to the cluster?

I have 3 node cluster. Node1 serves the traffic rest are for standby and quorum. I would like to upgrade them from 10.2 to 10.3.

10.2 -> Has xtrabackup
10.3 -> Has mariabackup

IST doesn't happen when I upgrade 3rd node from 10.2 to 10.3. But when I copy xtrabackup* files from 2nd node and use wsrep_sst_method=xtrabackup_v2. IST works.
When I change it to mariabackup and restart it doesn't work.

This is not what mariabackup is for. You backup and restore in the same "major" version (where "major" means it just means first 2 number of the version). If you need an upgrade, this is what mysql_upgrade is for.


Can't do any changes to a job in SQL 2000 I have a job in my SQL 2000 server, this job is used to execute a DTS, i want to change the schedule of this job.

but every time i try to change of delete or stop this job i get the error message

ERROR 14274: Cannot add, update, or delete a job (or its steps or schedules) that originated from an MSX server. The job was not saved.

i have a system shutdown coming up in the weekend and i cant stop this job...

It sounds like you might be facing this issue ...

http://support.microsoft.com/kb/281642

  After the Windows server name been changed, when trying to update or
  delete the jobs previously created in a SQL Server 2000 instance, you
  may receive the following error message:

  Error 14274: Cannot add, update, or delete a job (or its steps or
  schedules) that originated from an MSX server. The job was not saved.

Here are the suggested steps to workaround the issue ...

  The best way to handle this problem after the rename process is to
  follow these steps:

  Rename the server back to the original name.
  Script out all of the jobs and then delete them.
  Rename the server to the new name.
  Add back the jobs by running the script generated from step 2.

Additionally you can find more discussion/suggestions here ...

http://blog.sqlauthority.com/2006/12/20/sql-server-fix-error-14274-cannot-add-update-or-delete-a-job-or-its-steps-or-schedules-that-originated-from-an-msx-server-the-job-was-not-saved/


How to trim first digit off an integer value? i have data like Column1, i want to convert the Column1 data as Column2.
Can any one suggest to me how i can achieve this in SQL?

Column 1    Column2
1000001         1
1000002         2
1000003         3
1000004         4
1000005         5

Two options.

First you know what the first column is going to be.  In which case it's a simple

SET Column2 = Column1 - 1000000

or

SET Column2 = Column1 % 1000000

The second has the benefit that the digit could be 1-9 with no problems.

If you have no idea what that first place is going to be and you still only want to get rid of that one value you could do something like this.

declare @test int = 1005
select CAST(SUBSTRING(CAST(@test as varchar(50)),2,999) AS int)

If you have to allow for negative numbers it gets a bit more complicated.

declare @test int = -1005
select CASE WHEN @test < 0 THEN -1 ELSE 1 END *
        CAST(SUBSTRING(CAST(ABS(@test) as varchar(50)),2,999) AS int)

Obviously this could get rather slow for big sets of data.


Invalid object when selecting from temporary table I'm creating a temporary table called #PrevStatus. I've confirmed that the create statement is correct.

When I run,

exec (@TableQuery)

which contains the table create statement and then right after

select * from #PrevStates

I get,

Invalid object name '#PrevStatus'

Why is this happening?

An option which I've used before is to create the temp table using some known fields that will always be the same, then alter the temp table using dynamic SQL to add in the additional columns that you only need in specific cases.  This way the temp table is created within scope, then altered via dynamic SQL.

CREATE TABLE #MyTable (ID int identity(1,1) primary key)

if @Something = 1 then
begin
     set @sql = 'alter table #MyTable add column ...'
     exec (@sql)
end
...
Your dynamic select statement goes down here somewhere.


Safe backup of PXC / Galera cluster I have 3 nodes 'master master' cluster and using PXC 5.6

But I am only using a single node for both read and write.

Before using pxc, i was using standalone Mariadb 5.5 and I was taking backup using xtrabackup.

My question is, how should I take backup of PXC?
Should i take full and incremental backup for the node which is NOT used for read and write without any additional precautions just like i was taking for mariadb 5.5?

For e.g:
xtrabackup --backup --target-dir=/data/backup

Is there any officially recommended way to safely take backups of pxc?

I want to take backup so that if there is database corruption, I can start my cluster from scratch using that backup.

Here's another option:

mysql> SET GLOBAL wsrep_desync=1;
shell# <execute backup>
mysql> SET GLOBAL wsrep_desync=0;

Just make sure to take the backup from the non-write node and no traffic is going through the node that's running XtraBackup. Test first on non-production systems.

https://www.percona.com/doc/percona-xtradb-cluster/5.6/wsrep-system-index.html#wsrep_desync


Unexpected results with random numbers and join types I have a simple script that gets four random numbers (1 through 4) and then joins back to get the matching database_id number. When I run the script with a LEFT JOIN, I get four rows back every time (the expected result). However, when I run it with an INNER JOIN, I get a varying number of rows -- sometimes two, sometimes eight.

Logically, there shouldn't be any difference because I know rows with database_ids 1-4 exist in sys.databases. And because we're selecting from the random number table with four rows (as opposed to joining to it), there should never be any more than four rows returned.

This happens in both SQL Server 2012 and 2014. What is causing the INNER JOIN to return varying numbers of rows?

/* Works as expected -- always four rows */

SELECT rando.RandomNumber, d.database_id
FROM
  (SELECT 1 + ABS(CHECKSUM(NEWID())) % (4) AS RandomNumber
   FROM sys.databases WHERE database_id <= 4) AS rando
LEFT JOIN sys.databases d ON rando.RandomNumber = d.database_id;

/* Returns a varying number of rows */

SELECT rando.RandomNumber, d.database_id
FROM
  (SELECT 1 + ABS(CHECKSUM(NEWID())) % (4) AS RandomNumber
   FROM sys.databases WHERE database_id <= 4) AS rando
INNER JOIN sys.databases d ON rando.RandomNumber = d.database_id;

/* Also returns a varying number of rows */

WITH rando AS (
  SELECT 1 + ABS(CHECKSUM(NEWID())) % (4) AS RandomNumber
  FROM sys.databases WHERE database_id <= 4
)

SELECT r.RandomNumber, d.database_id
FROM rando AS r
INNER JOIN sys.databases d ON r.RandomNumber = d.database_id;

This might give some insight until one of the smarter folks on the site chimes in.

I put the random results into a temporary table and I consistently get 4 result regardless of the join type.

/* Works as expected -- always four rows */

DECLARE @Rando table
(
    RandomNumber int
);

INSERT INTO
    @Rando
(
    RandomNumber
)
-- This generates 4 random numbers from 1 to 4, endpoints inclusive
SELECT
    1 + ABS(CHECKSUM(NEWID())) % (4) AS RandomNumber
FROM
    sys.databases
WHERE
    database_id <= 4;

SELECT
    *
FROM
    @Rando AS R;

SELECT
    rando.RandomNumber
,   d.database_id
FROM
    @Rando AS rando
    LEFT JOIN
        sys.databases d
        ON rando.RandomNumber = d.database_id
ORDER BY 1,2;

/* Returns a varying number of rows */

SELECT rando.RandomNumber, d.database_id
FROM
    @Rando AS rando
    INNER JOIN
        sys.databases d
        ON rando.RandomNumber = d.database_id
ORDER BY 1,2;

/* Also returns a varying number of rows */

WITH rando AS
(
    SELECT * FROM @Rando AS rando
)
SELECT r.RandomNumber, d.database_id
FROM
    rando AS r
    INNER JOIN
        sys.databases d
        ON r.RandomNumber = d.database_id
ORDER BY 1,2;

If I compare query plans between your second query and the variation with a table variable, I can see there's a definite difference between the two. The red X is No Join Predicate so that seems really odd to my caveman developer brain

If I eliminate the random bit of the query to a constant  1 % (4), my plan looks better but the Compute Scalar was eliminated so that led me to look closer

It's computing the expression for the random number after the join. Whether that's expected, I still leave to the internal wizards on the site but at least that's why you're getting variable results in your join.

2014

For those playing along at home, the above query plans were generated from a 2008 R2 instance. The 2014 plans look different but the Compute Scalar operation remains after the join.

This is the query plan for a 2014 using the constant expression

This is the query plan for a 2014 instance using the newid expression.

This apparently is by design, Connect issue here. Thanks to @paulWhite for knowing that existed.


User cannot query cross-database, despite having permissions for both databases SQL Server 2017 Enterprise (14.0.3356.20), running on Windows Server 2016.
I have a situation where an application's user needs to join on a table in a separate database in the same instance, but I'm getting permissions errors when I try to run anything cross-database.  Basically, what it boils down to is that this works fine:
use DB2
EXECUTE AS user = 'domain\appUser'
SELECT TOP 1 * FROM dbo.Table
REVERT

But this doesn't:
use DB1
EXECUTE AS user = 'domain\appUser'
SELECT TOP 1 * FROM DB2.dbo.Table
REVERT

I get the following error on the second query:
Msg 916, Level 14, State 1, Line 3
The server principal "domain\appUser" is not able to access the database "DB2" under the current security context.

The user is mapped to both databases.  There are plenty of places elsewhere in our other applications that we have used this same kind of cross-database join, and they work fine.  Logged in as my admin user, this also works fine of course. I admittedly don't know too much about permissions in SQL Server, and this one has me stumped.

This is an artifact of your testing methodology.  You impersonated a user.  A user is a database-level principal.
Instead impersonate the login, or test with the target identity actually connecting to SQL Server.


what is the fastest way to import data into mysql 5.7 Now I want to import a 10000000+ record table from database A mysql cluster to B mysql cluster,now I am using :
source xx.sql

It is so slow. Now what is the fastest way to import? I want to import like this:

import a new table,like table_new(it should be very fast like copy data,may be 1000000/s)

remame old table to table_legacy

rename table_new to table

4.add index or constaint if needed
is it possible or what is the best way to complete this task?

Maybe this blog can help you about how to quickly restore MySQL logical backups.Click here


How can I know which tables "compose" a view? I want to create a trigger on SYS.database_mirroring as it is not a real table, just a view... I can't do it.

I want to know were the field mirroring_state_desc is coming from so that I can create a trigger on that table when it is changed.

How can I know which tables are used to create that view?

To take some action when a database mirroring state change occurs, you have several options including WMI and Event Notifications (using the DATABASE_MIRRORING_STATE_CHANGE event):

Altering on Database Mirroring Events

If you are doing any sort of work with mirroring, I can recommend (MVP & MCM) Robert Davis' book:

Pro SQL Server 2008 Mirroring


Can send test database mail but job emails failing I stopped receiving sql agent job failure notifications in the recent past. Upon investigation the error reported is:

The mail could not be sent to the recipients because of the mail server failure. (Sending Mail using Account 1 (2017-08-16T02:40:14). Exception Message: Could not connect to mail server. (No such host is known).

The mail account setup has not been changed and I verified it looks correct. Futhermore, I can successfully send and receive an email using the Send Test Email... function in SSMS. So why can't the jobs send the emails out?

Since the Send Test Email... function queues the email, it doesn't seem as there would be a permissions issue with sending it this way vs the job sending it. Also, that would indicate it is not a firewall issue. So the only difference is time of day...the jobs run in the middle of the night...but I can't think of any system configuration that would be changing then.

Any other ideas of things to check?

When you use Send Test Email, SSMS is using your credentials. When SQL Agent attempts to connect to the mail profile, it's using the SQL Agent service account credentials. That would be a major difference, and it wouldn't have anything to do with time of day...just credentials.
You can test it by scheduling a job that has a task that will fail (i.e. Select * from northwind) and let the SQL Agent run it, and find out if it will send the notification. That will at least eliminate the service account permission possibility...


Find Shoe Size Selection I'm developing a feature that allows users to input their foot dimensions and have the proper shoe size returned to them. I'm having difficulty deciding on the best way to implement this feature.

The query I have below is what I've come up with. It's returning the two closest results with the difference in length and width. The difference returned could be positive or negative, though, negative of course being an issue because that would indicate that the returned size is too small for the user's dimensions.

The piece I'm looking for help on is how to exclude sizes that would be smaller than the user input.

Query

SELECT
    size_name,
    length,
    ABS(length - ' . $input->length . ') AS length_difference,
    width,
    ABS(width - ' . $input->width . ') AS width_difference
FROM
    SIZES
LIMIT
    2
ORDER BY
    length_difference

Results

{
    "size_name": "8",
    "length": 10.3,
    "length_difference": 0.15,
    "width": 3.8,
    "width_difference": 0.4
},
{
    "size_name": "9",
    "length": 10.6,
    "length_difference": 0.15,
    "width": 3.9,
    "width_difference": 0.3
}

UPDATE

I've been tweaking and realized this doesn't have to be as hard as I was making it. In the screenshot below I have something close. I'm using PHP so I'm thinking I can just filter out the negative values and from there I clearly have size 9. Does this seem like an effective solution?

So it seems like you're missing the where clause which would filter out the undesirable results. In this example I'm assuming 2 is a decent threshold and using a between clause to limit the output. Greater than with a limit would also work but it really depends on the dataset.

SELECT
    size_name,
    length,
    ABS(length - ' . $input->length . ') AS length_difference,
    width,
    ABS(width - ' . $input->width . ') AS width_difference
FROM
    SIZES
WHERE
    length BETWEEN $input->length AND ($input->length + 2)
    AND width BETWEEN $input->width AND ($input->width + 2)

ORDER BY
    length_difference
LIMIT 2

Here's the example using greater than.

SELECT
    size_name,
    length,
    ABS(length - ' . $input->length . ') AS length_difference,
    width,
    ABS(width - ' . $input->width . ') AS width_difference
FROM
    SIZES
WHERE
    length > $input->length
    AND width > $input->width

ORDER BY
    length_difference
LIMIT 2


select items from multiple rows and add to one I need some help on SQL as this kind of selecting is beyond my knowledge. The result of the select should be in one row as it is shown in the picture. Can someone provide any ideas on how to achieve this?

if col_name1 =AA -> add col_name2 as value col_name5

if col_name1 =BB -> add col_name3 value as col_name6

if col_name1 =CC -> add col_name4  value of first CC (c31) as col_name7 and col_name4 of second CC (c31) value as col_name8

original table might not have all 4 ids

I'm making some assumptions about how your data is ordered (do all BB's come after AA's ordered by Id; do all CC's come after BB's ordered by Id), etc.
You also don't say what you want for the ID column in the final result, so I didn't include that column.

By using a Common Table Expression and the Row_Number Window Function, I'm added a sequential row number to each of your original rows.

Here's a demo set up script:

DECLARE @Data TABLE (
    ID INT
    ,col_name1 VARCHAR(10)
    ,col_name2 VARCHAR(10)
    ,col_name3 VARCHAR(10)
    ,col_name4 VARCHAR(10)
    )
INSERT INTO @Data (ID, col_name1, col_name2, col_name3, col_name4) VALUES
(1,'AA','a1','a2','a3'),
(2,'BB','b1','b2','b3'),
(3,'CC','c1','c2','c31'),
(4,'CC','c1','c2','c32'),
(5,'CC','c1','c2','c33'),
(6,'CC','c1','c2','c34')
;
with DataAndRowNumber as
(
select *,ROW_NUMBER () over(order by ID) as rn
from @Data
)
Select * from DataAndRowNumber

produces

| ID | col_name1 | col_name2 | col_name3 | col_name4 | rn |
|----|-----------|-----------|-----------|-----------|----|
| 1  | AA        | a1        | a2        | a3        | 1  |
| 2  | BB        | b1        | b2        | b3        | 2  |
| 3  | CC        | c1        | c2        | c31       | 3  |
| 4  | CC        | c1        | c2        | c32       | 4  |
| 5  | CC        | c1        | c2        | c33       | 5  |
| 6  | CC        | c1        | c2        | c34       | 6  |

From your question, it appears that you want col_name7 to be the first CC value, col_name8 to be the next CC value, col_name9 to be the next CC value and col_name10 to be the last CC value.  I'm assuming there will always be an 'AA' and a 'BB', so the first CC row is row number 3, the next CC row will be 4 and so on.

Putting it all together

DECLARE @Data TABLE (
    ID INT
    ,col_name1 VARCHAR(10)
    ,col_name2 VARCHAR(10)
    ,col_name3 VARCHAR(10)
    ,col_name4 VARCHAR(10)
    )
INSERT INTO @Data (ID, col_name1, col_name2, col_name3, col_name4) VALUES
(1,'AA','a1','a2','a3'),
(2,'BB','b1','b2','b3'),
(3,'CC','c1','c2','c31'),
(4,'CC','c1','c2','c32'),
(5,'CC','c1','c2','c33'),
(6,'CC','c1','c2','c34')
;
with DataAndRowNumber as
(
select *,ROW_NUMBER () over(order by ID) as rn
from @Data
)
select distinct
    (select col_name2 from DataAndRowNumber where rn=1) as col_name5,
    (select col_name2 from DataAndRowNumber where rn=2) as col_name6,
    (select col_name4 from DataAndRowNumber where rn=3) as col_name7,
    (select col_name4 from DataAndRowNumber where rn=4) as col_name8,
    (select col_name4 from DataAndRowNumber where rn=5) as col_name9,
    (select col_name4 from DataAndRowNumber where rn=6) as col_name10
from DataAndRowNumber

| col_name5 | col_name6 | col_name7 | col_name8 | col_name9 | col_name10 |
|-----------|-----------|-----------|-----------|-----------|------------|
| a1        | b1        | c31       | c32       | c33       | c34        |


Change the default version of MSSqlLocalDB for Visual Studio 2015 I am working on a C# project with SQL Server. For development, we use LocalDB instance of SQL Server. If something is changed in the database, we check-in the .mdf/.ldf files into TFS. The connection is made using MSSqlLocalDB.

The problem

After upgrades on some systems, we are now using VS 2013 and VS 2015, both for development. The issue started because of the fact that VS 2013 defaults to SQL Server 2014 and VS 2015 defaults to SQL Server 2016 for LocalDB instances.

Once the .MDF file has been opened with 2016, it becomes incompatible with 2014. Due to this, people using 2015 can't check-in the database changes.

To solve it, I installed Version 2014 of LocalDB on to machines with VS 2015 but unable to change the default version to which VS connects to.

CAN ANYONE HELP ME??

Thanks in advance...

It took me 2 days to find the solution to this problem.

Posting here for anybody who faces the issue in future.

I basically removed all instances of LocalDB from my system and reinstalled them.
Following are the steps that were required to finally solve the issue

Remove all the databases attached to the LocalDB 2016
Remove LocalDB 2016
Remove LocalDB 2014
Restart the system
Reinstall LocalDB 2014

For installation of LocalDB 2014, either use the standalone file from "https://www.microsoft.com/en-in/download/details.aspx?id=42299" OR
Select Express from standard Sql Server Setup and then select LocalDB in the component list


Is it possible to replicate data changes of a database with triggers using binary logs? I'm tasked to minimize the downtime of the database update. Unfortunately, I cannot use pt-online-schema-change toolkit because some tables in the current database has MySQL triggers that does further modification of data of other summary tables that may use past data.

I'm trying to research if this methodology is possible:

1.) Create a separate database for containing all new tables.
2.) Turn on binary logs
3.) Copy all data from old tables to new tables.
4.) Stop operation.
5.) Parse the binary logs and replicate the data changes to the new tables.
6.) Replace the old tables with the new tables
7.) Resume operation.

I tried enabling binary logs and set its format to STATEMENT. Unfortunately, it does not log the SQL statements executed by the triggers. Is there a way to log the SQL statements triggered in the binary logs?

It possible in theory with ROW binary logs, in this case each changes of tables will be write to the Log.

at this moment several companies declare this solution (plus many on GitHub):

MariaDB MaxScale CDC (Kafka) - http://126kr.com/article/5btzr54y53h
StreamSets - https://streamsets.com/products/sdc/  and https://streamsets.com/documentation/datacollector/latest/help/#Origins/MySQLBinaryLog.html#task_qbt_kyh_xx
Yelp - https://engineeringblog.yelp.com/2016/08/streaming-mysql-tables-in-real-time-to-kafka.html

You can search some other. Unfortunately at least my experience show - this products far from "ready to use" state. But this is possible direction for feature research.


What does Then 1 else 0 mean in a CASE expression used with an aggregate function? SUM(CASE WHEN column1 = 'value1' THEN 1 ELSE 0 END),
SUM(CASE WHEN column2 = 'value2' THEN 1 ELSE 0 END)..

I don't know why I am finding it difficult to understand what exactly is happening in the above statement and what exactly is 'Then 1 else 0' doing here.

If column1 contains the value value1 then the CASE expression will return 1, and SUM() will add 1 for that row. If it doesn't, the CASE expression will return 0, and it will add 0 for that row. This is a way to count how many rows have value1 in column1, but there are other ways to do this too, e.g. on 2012+:

SELECT COUNT(IIF(column1 = 'value1', 'truish', NULL))

On earlier versions:

SELECT COUNT(CASE WHEN column1 = 'value1' THEN 'truish' END)

(You don't need the ELSE here because, unlike SUM, COUNT ignores NULL.)

And I updated the value 1 to be a string to demonstrate that this has nothing to do with boolean.


Shrink ibdata1 and ib_logfile0 I want to reduce the size of the ibdata1 and ib_logfile0 files on my MySQL server 5.7. However, I do not want to activate the innodb_file_per_table option.

What would be the best option to reduce the size of above mentioned files without activating innodb_file_per_table?

ibdata1 is messy to shrink.  For 5.7, see  https://dev.mysql.com/doc/refman/5.7/en/innodb-resize-system-tablespace.html for thorough details on dumping, deleting, and reloading.  That's essentially the only option.

You may as well change innodb_log_file_size (in my.cnf) before the load.

To resize just iblog* --

innodb_fast_shutdown = 0
do graceful shutdown
change innodb_log_file_size
start

I recommend innodb_file_per_table=ON for 'large' tables, at least when doing CREATE or ALTER.  Then maintenance on such tables not bloat ibdata1.  (I prefer OFF for small tables.  Yeah, it is messy to have some tables FPT, some not.)

Note: changing FPT is not a way to shrink ibdata1; ibdata1 only grows.

I recommend not letting free disk space drop below the size of the largest table.


deploy data from sql 2016 backup to azure database I've got backup data from a SQL 2016 server that i want to move/restore onto an azure database.  I'm not seeing an in-house option to do this anywhere, so it looked like the best option was to restore locally, build schema/data scripts for the data, and then run the script on the azure database.

Only problem is that the script that i get back is around 4GB large.  it won't load in sql management studio or azure's own 'query editor' page, and if i deploy via sqlcmd it crashes halfway w/out an easy way to figure out where it crashed at and how to resume from that point.

Are there good methods out there to do this?  I plan on needing to do this several times, so i'd like to get my methods down.  Thank you!

You need a live DB to export to Azure, which would require you to restore it locally. Once that is done, you can create a .BACPAC file on Azure storage and restore it, or set up transactional replication, and there are a few other methods as well.

The .BACPAC migration process looks to be the simplest, even if it isn't that simple. Honestly, this page explains it better than I can. Bruno Terklay is at least a minor demigod over at Microsoft, and this is really well written. All credit to him, not me.


Sybase server build error, missing library files I'm trying to build sybase ase server through sample resource file and i got this error. "error loading shared libraries: libXm.so.4: cannot open
shared object file: No such file or directory". So ran ldd on srvbuild and found below, two of them are missing.. any idea how to resolve this?

The OS is RHEL6.5 64bit and its ASE 16, express edition.

environment variables set.

It should be enough to install missing libraries on you system.

libXm is part of openmotif package:

libXm.so.4 (32bit)

libXm.so.4 (64bit)

libXp is part of X.Org X11 runtime library:

libXp.so.6 (32bit)

libXp.so.6 (64bit)

EDIT: I created a support case on SAP site and they responded me with a list of necessary packages. First, there is a site System requirements for Linux (this one is for ASE 16.0 SP03 PL05, but you can change it in the upper right corner) where you can find that you need to setup $LANG environment variable first. Then there is referral to SAP KBA Note 2489781, which is accessible only with valid credentials, so I picked important information below (again valid for ASE 16.0 SP03 PL05):

System: Red Hat

Hardware: x86_64 processor (Intel Xeon with EM64T)

Libraries:

kernel-2.6.32-71.el6.x86_64
glibc-2.12-1.7.el6.x86_64
compat-glibc-2.5-46.2.x86_64
glibc.i686 (Redhat 7 requires 32-bit libraries to execute the isql and dscp utilities)

Additional Linux Libraries:

openmotif-2.3.1-2.el5
libXp-1.0.0-8.1.el5
libXt-1.0.2-3.1.fc6
libXtst-1.0.1-3.1
libXi-1.6.1-1
libXmu-1.0.2-5
libXext-1.0.1-2.1
libSM-1.0.1-3.1
libICE-1.0.1-2.1
libX11-1.0.3-9.el5

With all of above installed, you should be able to build and run this version of ASE.


How do I find all tables in a DB that have no explicit primary key? A Google search spewed forth millions of hits on how to find tables without clustered indexed, the PK normally being the clustered index of a table. However, a table could easily have a natural key as a clustered index, and a non-clustered surrogate index, like an identity column.

How do I find all tables in a DB without a primary key defined? I have 245 tables in this DB: manual inspection is grossly inefficient.

Couple ways to skin this cat but this works fine in SQL Server 2005 and up, and I find it a pain free way to handle the problem -

The OBJECTPROPERTY() function can list various properties about objects - like tables. One of those properties is whether or not a table has a primary key.

OBJECTPROPERTY(object_id, tablehasprimarykey) = 0 would be a table without a primary key.

So

SELECT OBJECT_SCHEMA_NAME( object_id ) as SchemaName, name AS TableName
FROM sys.tables
WHERE OBJECTPROPERTY(object_id,'tablehasprimaryKey') = 0
ORDER BY SchemaName, TableName ;

Should give you what you need. You can see all about the other ways to use the OBJECTPROPERTY() function in books online. This is the 2012 version of the article.


The database in recovery I use a database named BI, every night it is restored. It worked great but since some days ago it is shown that the database is in recover. I used
RESTORE <BI> WITH RECOVERY

but it gives me
RESTORE cannot operate on database 'BI' because it is configured for database mirroring or has joined an availability group. If you intend to restore the database, use ALTER DATABASE to remove mirroring or to remove the database from its availability group.

does anybody has other ideas to solve this problem?

Does anybody has other ideas to solve this problem?

Do as error message says, perhaps you connected wrong SQL Instance this time.
Using following query, you can verify whether the database part of AG or not:
select name, IIF(group_database_id IS NULL, 'NO', 'YES') is_joined_AG
from sys.databases;

select DB_NAME (database_id) mirrored_db
from sys.database_mirroring
where mirroring_guid is not null;


Create a mysql event with condition I currently have a mysql database where I keep data about rented books. When somebody rents a book, I save on the database an expiration date.

So, I want to create an mysql event to update the book status when it is not returned in time. How can I create this event?

Here's what I'm trying:

CREATE EVENT `EXPIRE` ON SCHEDULE EVERY 1
 HOUR STARTS '2016-07-16 00:00:00.000000' ENDS '2018-07-29
00:00:00.000000' ON COMPLETION NOT PRESERVE ENABLE DO DO IF
mtl_rents.dateLimit=CURRENT_DATE THEN
        UPDATE mtl_rents SET mtl_rents.status='Expired'
    END IF

But I'm getting Syntax errors on the IF.

You have double DO at the end of the third line.


PostgreSQL fsync off for a single database I have a write-intensive database stored in PostgreSQL v9.4.4 and it causes IO on my device. I'd like to switch fsync off for only a single database, not PostgreSQL server wide. (I accept that it can cause corruption when unexpected shutdown occurs). Help me find out how to make it.

You might want to check unlogged tables, they do not produce WALs at all. But of course if crash happen - all data from them will be lost and they cannot be replicated.

Also there is much safer setting synchronous_commit which could reduce IO and it's possible to set it per session or per user/database.


Best strategies to have a backup of an RDS MySQL database I have an AWS RDS MySQL database. I'm not a DBA, so my knowledge is limited.

I wish to design strategies to have zero data loss in case of a failure, in terms of replication or data backup.

I know they are different terms. (And ignoring that a wrong delete statement can delete data in which case replication may not be useful).

All I wish to do is have a zero data loss when there is a database failure.
AWS maintains snapshots but that may be few hours old. So there is data loss.

Should I consider setting a DB server outside AWS? Or what? Should I
do replication or take regular backup?

Any other strategies that DBAs have in mind?

You may not be aware of this but MySQL RDS allows you to create two things

Read Replicas
Snapshots

What is the difference between them?

A read replica is simply a MySQL Replication Slave that you create from the AWS console. It's just a point and click away. It maintains a continuous backup.
A snapshot is simply an instance copy of an AWS MySQL RDS Instance. This is more of a point-in-time copy of the data. This can serve as a physical backup of the instance.
CAVEAT #1 : You could combine both methods by taking snapshots of read replicas so as not to disturb the production RDS instance. You can take snapshots of a production instance but it might be a little slower than taking the snapshot of the read replica.
CAVEAT #2 : You don't want mysqldumps from RDS because there may be applicable charges for doing so. If you really need access to mysqldumps, switch from RDS to EC2. You just have to be more hands on managing MySQL (Should I increase max_connections in AWS RDS t1-micro for MySQL?) as well as managing the EC2 instance.

Here are some of my past posts on this subject

Dec 11, 2012 : MySQL on RDS, data transfer from one instance to another as a production job
Jul 25, 2012 : Scaling Percona datacenters: setup and replication

UPDATE 2013-05-22 16:21 EDT
Replication lag is a part of MySQL Replication because it is asynchronous. Even though you are using MySQL 5.5, you cannot take advantage of semisynchronous replication features (evidently AWS policy).
If you would like virtual synchronous replication, you may want Percona XtraDB Cluster (PXC), an all-InnoDB virtually synchronous ACID-compliant DB Cluster, since replication lag is forbidden by design (Codership's Galera libraries are embedded in the Server Binaries). You will have to stop using RDS and go with EC2.
Check out PXC's Documentation on how to setup PXC in the EC2 environment.


Combining table under different alias I have two tables trips and users. Whenever an user posts a trip, it stores id of the user as foreign key. And while fetching, I am joining those two tables the following way.
Query:
SELECT title, starting_point, ending_point, distance, trips.created_on
FROM trips
  JOIN users ON (users.id=trips.created_by);

Result:
{
            "title": "lorem ipsum",
            "starting_point": "Location A",
            "ending_point": "Location B",
            "distance": 20,
            "created_on": "2020-07-27T18:15:00.000Z",
            "author": "Test user"
        },

Result needed.
{
            "title": "lorem ipsum",
            "starting_point": "Location A",
            "ending_point": "Location B",
            "distance": 20,
            "created_on": "2020-07-27T18:15:00.000Z",
            "author": {
                       "id":1
                       "name": "Test user",
                       "role": "admin"
                      }
        },

In short, I want to join user table with trips table but I want to separate the content of user table in different alias in trip.

Whole row
You don't mention JSON, but it seems you are after to_json(b)() or similar. Example:
SELECT t.*, to_json(u) AS author
FROM   trips t
JOIN   users u ON u.id = t.created_by;

Or, without involving JSON:
SELECT t.*, u AS author
FROM   trips t
JOIN   users u ON u.id = t.created_by;

Then author is a ROW value, but without column names - that information is only in the definition of the row type.
Selected columns
You later commented:

... if I want id, email and name only from users table?

JSON:
SELECT t.*
     , json_build_object('id', u.id, 'email', u.email, 'name', u.name)) AS author
FROM   trips t
JOIN   users u ON u.id = t.created_by;

Or ROW value:
SELECT t.*
    , (SELECT x FROM (SELECT u.id, u.email, u.name) AS x)) AS author
FROM   trips t
JOIN   users u ON u.id = t.created_by;

See:

Return as array of JSON objects in SQL (Postgres)
Select columns inside json_agg
Raw SQL queries in a REST Api


Database structure for bonus system in MySQL I have a problem with creating the structure for my database in MySQL as I'm new in creating databases. The problem is in this: there are three tables sellers, clients and bonus. Table sellers stores list of sellers with some additional info and table clients stores list of clients also with additional info. Last table bonus should store bonus accounts of clients regarding sellers, i.e. each client can have multiple bonus account (but not more than one bonus account for a seller):

Sellers                    Clients

id    name                 id    name
----------------           ------------
1     McDonalds            1     John
2     Starbucks            2     Bob
3     IKEA                 3     Anna
4     Apple

For example, Bob have two bonus accounts, one for IKEA and one for McDonalds and Anna have four bonus accounts, i.e. for all sellers and so on. All bonus accounts stored in the table bonus.

How to design table bonus in order to apply it as in above mentioned example?

You have to make complex primary index that refers to the other tables:

CREATE TABLE `bonuses` (
    `c_id` INT(10) UNSIGNED NOT NULL,
    `s_id` INT(10) UNSIGNED NOT NULL,
    `bonus` INT(11) NULL DEFAULT NULL,
    PRIMARY KEY (`c_id`, `s_id`),
    CONSTRAINT `FK_sellers` FOREIGN KEY (`s_id`) REFERENCES `sellers` (`id`),
    CONSTRAINT `FK_customers` FOREIGN KEY (`c_id`) REFERENCES `customers` (`id`)
)
ENGINE=InnoDB;

Primary keys are always unique thus you can't create the pair seller-customer that already exists. Foreign keys with restrictions prevents creation of pair with nonexistent seller/customer/both.


Upgrading to new cPanel Server with mySQL on SSD -- What should I change in my.cnf? I'm upgrading to a faster server with whm/cpanel and the mysql databases will be on a SSD. I've read a bit about not wanting any query cache and stuff like that, but as a novice, I've learned that tinkering with my.cnf can be disastrous.

Here's the current my.cnf for my "hard drive" based webserver.

[mysqld]
query_cache_limit=32M
query_cache_size=128M
query_cache_type=1
query_cache_min_res_unit=4096
open_files_limit=16000

table_open_cache=25000
table_definition_cache=2000
key_buffer_size=128M
innodb_buffer_pool_size=2000M
innodb_buffer_pool_instances=2
join_buffer_size=4M
sort_buffer_size=4M
read_rnd_buffer_size=4M
tmp_table_size=3048M
max_heap_table_size=3048M
thread_cache_size=256

max_connections=250
table_cache=60000

What would you change in it on if mysql is on a SSD?

Here's a bit of info from tuner.sh all:

Avg. qps = 447

WORKER THREADS
Current thread_cache_size = 256
Current threads_cached = 96
Current threads_per_sec = 0
Historic threads_per_sec = 0
Your thread_cache_size is fine

MAX CONNECTIONS
Current max_connections = 250
Current threads_connected = 20
Historic max_used_connections = 116
The number of used connections is 46% of the configured maximum.
Your max_connections variable seems to be fine.

INNODB STATUS
Current InnoDB index space = 794 M
Current InnoDB data space = 1.10 G
Current InnoDB buffer pool free = 14 %
Current innodb_buffer_pool_size = 1.95 G
Depending on how much space your innodb indexes take up it may be safe
to increase this value to up to 2 / 3 of total system memory

MEMORY USAGE
Max Memory Ever Allocated : 3.62 G
Configured Max Per-thread Buffers : 3.02 G
Configured Max Global Buffers : 2.21 G
Configured Max Memory Limit : 5.23 G
Physical Memory : 15.56 G
Max memory limit seem to be within acceptable norms

KEY BUFFER
Current MyISAM index space = 222 M
Current key_buffer_size = 128 M
Key cache miss rate is 1 : 1115
Key buffer free ratio = 72 %
Your key_buffer_size seems to be fine

QUERY CACHE
Query cache is enabled
Current query_cache_size = 128 M
Current query_cache_used = 28 M
Current query_cache_limit = 32 M
Current Query cache Memory fill ratio = 22.42 %
Current query_cache_min_res_unit = 4 K
Your query_cache_size seems to be too high.
Perhaps you can use these resources elsewhere
MySQL won't cache query results that are larger than query_cache_limit in size

SORT OPERATIONS
Current sort_buffer_size = 4 M
Current read_rnd_buffer_size = 4 M
Sort buffer seems to be fine

JOINS
Current join_buffer_size = 4.00 M
You have had 166 queries where a join could not use an index properly
join_buffer_size >= 4 M
This is not advised
You should enable "log-queries-not-using-indexes"
Then look for non indexed joins in the slow query log.

OPEN FILES LIMIT
Current open_files_limit = 120260 files
The open_files_limit should typically be set to at least 2x-3x
that of table_cache if you have heavy MyISAM usage.
Your open_files_limit value seems to be fine

TABLE CACHE
Current table_open_cache = 60000 tables
Current table_definition_cache = 2000 tables
You have a total of 733 tables
You have 851 open tables.
The table_cache value seems to be fine

TEMP TABLES
Current max_heap_table_size = 2.97 G
Current tmp_table_size = 2.97 G
Of 262494 temp tables, 33% were created on disk
Perhaps you should increase your tmp_table_size and/or max_heap_table_size
to reduce the number of disk-based temporary tables
Note! BLOB and TEXT columns are not allow in memory tables.
If you are using these columns raising these values might not impact your
ratio of on disk temp tables.

TABLE SCANS
Current read_buffer_size = 128 K
Current table scan ratio = 1005 : 1
read_buffer_size seems to be fine

TABLE LOCKING
Current Lock Wait ratio = 1 : 101097
Your table locking seems to be fine

The OLD web server is an 8 core single Xeon @ 16GB ram.  The NEW web server is a 24 core dual Xeon @ 64GB ram.  The SSD on the NEW server will contain the OS and the Database only, and all the sites are WordPress based resulting in about 250,000 pageviews total a day.

Thanks so much for any help or suggestions!

Regards,

Ben

open_files_limit=16000

It makes little sense to set this lower than table_open_cache. See the default value described in https://dev.mysql.com/doc/refman/5.6/en/server-system-variables.html#sysvar_open_files_limit

  table_open_cache=25000
  table_cache=60000

You probably don't need to set this so high considering you only have < 900 tables open.

Note that table_cache is an obsolete synonym for table_open_cache. The last one in the my.cnf file takes priority. So you have set your table_open_cache to 60000, overwriting the earlier setting. I suggest deciding on the value you want and use one line to set it, to avoid confusion.

  table_definition_cache=2000

This is fine.

  key_buffer_size=128M

The key buffer is only for MyISAM. If you have no MyISAM tables, the default of 8M is fine.

If you have lots of MyISAM tables, you should convert them to InnoDB. ;-)

  innodb_buffer_pool_size=2000M

Since you upgraded to 64G, this seems low. On the other hand, you have only about 2G of InnoDB data and indexes, which is not unusual for a simple Wordpress site. There's no benefit in increasing the buffer pool any higher.

  innodb_buffer_pool_instances=2

The default number of BP instances is 8 as of MySQL 5.6.6. Since you have 24 cores, you might want to split the BP at least up to 8 instances.

  join_buffer_size=4M

16x the default of 256K seems too high. Ideally, you shouldn't need to rely on the join buffer because joins should be handled by indexes.

In MySQL 5.6, the join buffer is also used for Batched Key Access optimization, but this doesn't kick in very often.

  sort_buffer_size=4M

Also 16x the default of 256K. Useful for avoiding sort merge passes when you do filesorts of large tables. Monitor SHOW GLOBAL STATUS LIKE 'Sort_merge_passes', but I bet you can decrease this.

  read_rnd_buffer_size=4M

Also 16x the default of 256K. Used for MyISAM tables, or for Multi-Range Read optimization for any storage engine. Very rare to need this.

  tmp_table_size=3048M
  max_heap_table_size=3048M

These are probably excessive. I start out small, and increase tmp_table_size if I see a steady increase in SHOW GLOBAL STATUS LIKE 'Created_tmp_disk_table'.

  thread_cache_size=256

Probably unnecessary to have a thread cache at all on your system. The default would be 33. On a modern version of Linux, creating a new thread is quick anyway, so the need for the thread cache is not as important is it is on Solaris, FreeBSD, etc.

  max_connections=250
  table_cache=60000

Note that table_cache is an obsolete synonym for table_open_cache. The last one in the my.cnf file takes priority. So you have set your table_open_cache to 60000, overwriting the earlier setting. I suggest deciding on the value you want and use one line to set it, to avoid confusion.