phpmyadmin – como obtener el usuario de un sistema web para usarlo en un trigger mysql

estoy realizando un sistema web que requiere de auditoria, necesito tomar el usuario que inicio sesión para usarlo en los trigger de las tablas para saber quien esta haciendo los cambios.puedo obtenerlo en php con:


pero no se como llevarlo al trigger cuando se guarda algo en la base de datos.

MySQL Database Schema recommendations? – Database Administrators Stack Exchange

enter image description here

Hello, all! I have several needs that I’m attempting to accomplish with my database implementation.

  1. List number of wins / losses for a particular user
  2. Keep track of the amount of coins a user has
  3. Allow user to purchase items from a shop
  4. List profitability from all gambling-based games

For my use case, I have several games the end user can play. The “Fun” category of games does not award ‘coins’; however, I want users to be able to list how many they’ve won. All the gambling-based games allow players to earn / lose their ‘coins’ when playing.

  • Fun Games:

    • Rock, Paper, Scissors
    • Connect Four
    • TicTacToe
  • Gambling Games

    • Slot Machine (Slots)
    • Black Jack
    • HiLo
    • Coin Flip

After doing some research online, I have implemented a ‘match’ table to keep track of all the matches between players. With two player game sessions, two rows will be created. If I want to know how many wins a player has, I can use a sum(wins) from the match table. Additionally, from the ‘gambling’ match table, I can run a similar query to pull the profitability loss / gain. Is there a better way of doing this?

Additionally, if a user profited from a gambling match – I’m assuming I’d need to perform two operations:

  • Add new row in GamblingMatch
  • Update amount of coins a player has

At this moment, I’m not sure of a way to do that in one operation.

banco de dados – Como criar uma coluna auto incremente no MySql porém com uma informação já definida previamente?

Estou criando um sistema de cadastro com php e mysql e preciso que quando os novos usuários realizarem registro que eles recebam um link e um código que eu já tenha determinado antes além das informações que eles irão add ao se cadastrarem.

usuario 1 / link tal / codigo tal
usuario 2 /link tal / codigo tal
usuario 3 / link tal / codigo tal

como se a tabela já estivesse com as linhas e colunas prontas com os links e códigos já nela e apenas quando um usuário realizar um registro novo no site a conta dele preencha uma linha onde terá as colunas em branco para as informações que ele digitar porem a coluna de link e código já estavam preenchidas previamente para o usuário que se registrasse ali, ou que ele puxasse em ordem correta de uma lista os links e códigos e apenas fosse distribuindo para os usuários que fossem se cadastrando.

mysql – MariaDB crashed: Unknown/unsupported storage engine: InnoDB

I’ve a Debian GNU/Linux 9 (4GB, 2 CPUs) on Digitalocean. Tonight (I’ve done nothing) my DB (mariaDB) crashed with this errors. I ran a wordpress with InnoDB and myISAM tables:

2020-10-17  0:51:18 140430430813568 (Note) InnoDB: Using mutexes to ref count buffer pool pages
2020-10-17  0:51:18 140430430813568 (Note) InnoDB: The InnoDB memory heap is disabled
2020-10-17  0:51:18 140430430813568 (Note) InnoDB: Mutexes and rw_locks use GCC atomic builtins
2020-10-17  0:51:18 140430430813568 (Note) InnoDB: GCC builtin __atomic_thread_fence() is used for memory barrier
2020-10-17  0:51:18 140430430813568 (Note) InnoDB: Compressed tables use zlib 1.2.8
2020-10-17  0:51:18 140430430813568 (Note) InnoDB: Using Linux native AIO
2020-10-17  0:51:18 140430430813568 (Note) InnoDB: Using SSE crc32 instructions
2020-10-17  0:51:18 140430430813568 (Note) InnoDB: Initializing buffer pool, size = 500.0M
InnoDB: mmap(549126144 bytes) failed; errno 12
2020-10-17  0:51:18 140430430813568 (ERROR) InnoDB: Cannot allocate memory for the buffer pool
2020-10-17  0:51:18 140430430813568 (ERROR) Plugin 'InnoDB' init function returned error.
2020-10-17  0:51:18 140430430813568 (ERROR) Plugin 'InnoDB' registration as a STORAGE ENGINE failed.
2020-10-17  0:51:18 140430430813568 (Note) Plugin 'FEEDBACK' is disabled.
2020-10-17  0:51:18 140430430813568 (ERROR) **Unknown/unsupported storage engine: InnoDB**
2020-10-17  0:51:18 140430430813568 (ERROR) Aborting

My full DB conf:

# These groups are read by MariaDB server.
# Use it for options that only the server (but not clients) should see
# See the examples of server my.cnf files in /usr/share/mysql/

# this is read by the standalone daemon and embedded servers

# this is only for the mysqld standalone daemon

# * Basic Settings
user        = mysql
pid-file    = /var/run/mysqld/
socket      = /var/run/mysqld/mysqld.sock
port        = 3306
basedir     = /usr
datadir     = /var/lib/mysql
tmpdir      = /tmp
lc-messages-dir = /usr/share/mysql

# Instead of skip-networking the default is now to listen only on
# localhost which is more compatible and is not less secure.
bind-address        =

# * Fine Tuning
key_buffer_size     = 16M
max_allowed_packet  = 16M
thread_stack        = 192K
thread_cache_size       = 8
# This replaces the startup script and checks MyISAM tables if needed
# the first time they are touched
myisam_recover_options  = BACKUP
#max_connections        = 100
#table_cache            = 64
#thread_concurrency     = 10

innodb_buffer_pool_instances = 1
innodb_buffer_pool_size = 500M
max_heap_table_size     = 25M
tmp_table_size          = 25M
#log_slow_queries        = /var/log/mysql/mysql-slow.log
#long_query_time = 2

# * Query Cache Configuration
query_cache_limit   = 2M
query_cache_size        = 50M

# * Logging and Replication
# Both location gets rotated by the cronjob.
# Be aware that this log type is a performance killer.
# As of 5.1 you can enable the log at runtime!
#general_log_file        = /var/log/mysql/mysql.log
#general_log             = 1
# Error log - should be very few entries.
log_error = /var/log/mysql/error.log
# Enable the slow query log to see queries with especially long duration
#slow_query_log_file    = /var/log/mysql/mariadb-slow.log
#long_query_time = 10
#log_slow_rate_limit    = 1000
#log_slow_verbosity = query_plan
# The following can be used as easy to replay backup logs or for replication.
# note: if you are setting up a replication slave, see README.Debian about
#       other settings you may need to change.
#server-id      = 1
#log_bin            = /var/log/mysql/mysql-bin.log
expire_logs_days    = 10
max_binlog_size   = 100M
#binlog_do_db       = include_database_name
#binlog_ignore_db   = exclude_database_name

# * InnoDB
# InnoDB is enabled by default with a 10MB datafile in /var/lib/mysql/.
# Read the manual for more InnoDB related options. There are many!

# * Security Features
# Read the manual, too, if you want chroot!
# chroot = /var/lib/mysql/
# For generating SSL certificates you can use for example the GUI tool "tinyca".
# ssl-ca=/etc/mysql/cacert.pem
# ssl-cert=/etc/mysql/server-cert.pem
# ssl-key=/etc/mysql/server-key.pem
# Accept only connections using the latest and most secure TLS protocol version.
# ..when MariaDB is compiled with OpenSSL:
# ssl-cipher=TLSv1.2
# ..when MariaDB is compiled with YaSSL (default in Debian):
# ssl=on

# * Character sets
# MySQL/MariaDB default is Latin1, but in Debian we rather default to the full
# utf8 4-byte character set. See also client.cnf
character-set-server  = utf8mb4
collation-server      = utf8mb4_general_ci

# * Unix socket authentication plugin is built-in since 10.0.22-6
# Needed so the root database user can authenticate without a password but
# only when running as the unix root user.
# Also available for other users if required.
# See

# this is only for embedded server

# This group is only read by MariaDB servers, not by MySQL.
# If you use the same .cnf file for MySQL and MariaDB,
# you can put MariaDB-only options here

# This group is only read by MariaDB-10.1 servers.
# If you use the same .cnf file for MariaDB of different versions,
# use this group for options that older servers don't understand

Here my first page of htop
htop result after some minutes
Could not export/copy htop result.

I would be happy if you could help me out!!

Thanks a lot

c# – Configurar MySQL server no Windows server 2008

Olá pessoal tenho uma aplicação desktop feito em c# e MySQL está tudo pronto porém , não consigo acessá-lo remotamente por meio de um servidor Windows server 2008 R2 .

Alguém ?????

Restore MySQL database partially – Database Administrators Stack Exchange

I am using WordPress to run my website, along with the traditional MySQL Databases. Whilst seeking to speed up my website, I decided to clear some of the old and unused tables with WP-Optimizer. Sadly, it seems that I have deleted something I shouldn’t by mistake, since it seems I have partially broken Elementor Plugin.

I luckily have a database backup, but it is a few days old and, given there are a lot of modifications done on a single day, I do not wish to simply import that backup; what I’d like, is to compare my website’s database with my backup and restore the missing tables only, after thorough examination of their content.

I am no SQL expert at all and looking for guidance.

Is it possible to connect to MySQL which is in the Cloud Run container remotely?

Is it possible to connect to MySQL which is in the Cloud Run container remotely?


Server 1 (slave server) - I have a Docker container (in Google Cloud Run) with MySQL-Server in it.
Server 2 (main server) - I want to connect to MySQL via PHP.

Is it possible to do this?


I trying it to do, but I can not.

mysql – Issues with store procedure and system

I take this opportunity to send you a greeting and I comment you, Im working in a store procedure using system inside of MySQL but in workbench, it appears me, missing ;, extraneous input. These are my variables with which; im playing:

/*Modify the delimiter by default*/
/*Dropping the store procedure if it exists on the database*/
DROP PROCEDURE IF EXISTS proc_statistics_mysql$$
/*Creating the store procedure*/
CREATE PROCEDURE proc_statistics_mysql()
/*Defining the security clause to check the privilege users*/
/*The store procedure contains only SQL language*/
/*Begining my store procedure*/
/*Declaring my variables with which im gonna work*/
    DECLARE var_os_version VARCHAR(100) DEFAULT '';
    DECLARE var_ip_addr INT DEFAULT 0;
    DECLARE var_hostname VARCHAR(100) DEFAULT '';
    DECLARE var_cpuinfo VARCHAR(100) DEFAULT '';
    DECLARE var_memtotal INT DEFAULT 0;
    DECLARE var_memfree INT DEFAULT 0;
    DECLARE var_memavailable INT DEFAULT 0;
    DECLARE var_totaldisk INT DEFAULT 0;
    DECLARE var_freedisk INT DEFAULT 0;
    DECLARE var_usedisk INT DEFAULT 0;
    DECLARE var_mysql_version VARCHAR(100) DEFAULT '';
    DECLARE var_mysql_dbs VARCHAR(100) DEFAULT '';
/*Assign the values to the variables*/
    SET var_os_version=(system cat /etc/os-release | grep -i 'Pretty_Name' | cut -d '"' -f2);
    SET var_ip_addr=(system hostname -I | awk '{print $1}');
    SET var_hostname=(system hostname);
    SELECT var_os_version,var_ip_addr,var_hostname;
/*Finishing my store procedure*/
/*Modifying the delimiter to the default*/

I want to collect some data of operative system and store inside a database using a store procedure to get the data

Why MyBatis query big table timeout but fast in Sequel Pro in MySQL 5.7

I am using MySQL 5.7, today this is a long time query stuck me. this is my table DML:

CREATE TABLE `h_round_pump_record` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT '',
  `tenant_id` bigint(20) NOT NULL COMMENT '',
  `tenant_name` varchar(256) NOT NULL DEFAULT '' COMMENT '',
  `created_time` bigint(20) NOT NULL COMMENT '',
  `updated_time` bigint(20) NOT NULL COMMENT '',
  `app_id` bigint(11) NOT NULL COMMENT '',
  `app_mark` varchar(256) NOT NULL DEFAULT '' COMMENT '',
  `app_name` varchar(256) NOT NULL DEFAULT '' COMMENT '',
  `settlement_time` bigint(20) NOT NULL COMMENT '',
  `biz_id` bigint(20) NOT NULL COMMENT '',
  `room_play_id` bigint(20) NOT NULL COMMENT '',
  `room_amount_fmt` varchar(256) NOT NULL DEFAULT '' COMMENT '',
  `tenant_revenue` bigint(20) GENERATED ALWAYS AS ((`real_people_revenue` + `robot_profit`)) STORED,
  PRIMARY KEY (`id`),
  UNIQUE KEY `biz_id` (`biz_id`,`tenant_id`,`app_id`),
  KEY `round_record_idx` (`settlement_time`,`having_real_people`,`tenant_id`,`app_id`)

and this is my query sql:

select *
    from h_round_pump_record 
     WHERE (  settlement_time <= 1601481599000
                  and settlement_time >= 1598889600000
                  and tenant_id = 
                  and app_id in
        ) )

to speed up query, I create a index in settlement_time. In Sequel Pro the SQL will return result in 3 seconds, but In apps query using MyBatis, it will take 30s to return, why the MyBatis query takes so long(the sql is the same because I am copied from slow_log table)? This is my slow log settings:

set global slow_query_log=ON
set global log_output='table';
set long_query_time = 5;
select * 
from mysql.slow_log;

this is the log output of MyBatis:

### Error querying database.  Cause: com.mysql.cj.jdbc.exceptions.MySQLTimeoutException: Statement cancelled due to timeout or client request
### The error may exist in class path resource (mybatis/mapper/illidan/RoundPumpRecordMapper.xml)
### The error may involve com.sportswin.soa.illidan.hub.dao.RoundPumpRecordMapper.selectByExample-Inline
### The error occurred while setting parameters
### SQL: select           'true' as QUERYID,                id, tenant_id, tenant_name, agent_id, agent_name, created_time, updated_time, app_id,      app_mark, app_name, settlement_time, biz_id, room_play_id, room_amount_fmt, ratio,      tenant_revenue, real_pump_amount, real_people_profit, robot_profit, robot_revenue,      real_people_revenue, having_real_people         from h_round_pump_record                         WHERE (  settlement_time <= ?                                                                and settlement_time >= ?                                                                and tenant_id = ?                                                                and app_id in                   (                     ?                   ) )
### Cause: com.mysql.cj.jdbc.exceptions.MySQLTimeoutException: Statement cancelled due to timeout or client request
; Statement cancelled due to timeout or client request; nested exception is com.mysql.cj.jdbc.exceptions.MySQLTimeoutException: Statement cancelled due to timeout or client request
    at ~(spring-jdbc-5.1.13.RELEASE.jar!/:5.1.13.RELEASE)
    at ~(spring-jdbc-5.1.13.RELEASE.jar!/:5.1.13.RELEASE)
    at ~(spring-jdbc-5.1.13.RELEASE.jar!/:5.1.13.RELEASE)
    at org.mybatis.spring.MyBatisExceptionTranslator.translateExceptionIfPossible( ~(mybatis-spring-2.0.2.jar!/:2.0.2)
    at org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke( ~(mybatis-spring-2.0.2.jar!/:2.0.2)
    at com.sun.proxy.$Proxy178.selectList(Unknown Source) ~(na:na)
    at org.mybatis.spring.SqlSessionTemplate.selectList( ~(mybatis-spring-2.0.2.jar!/:2.0.2)
    at org.apache.ibatis.binding.MapperMethod.executeForMany( ~(mybatis-3.5.2.jar!/:3.5.2)
    at org.apache.ibatis.binding.MapperMethod.execute( ~(mybatis-3.5.2.jar!/:3.5.2)
    at org.apache.ibatis.binding.MapperProxy.invoke( ~(mybatis-3.5.2.jar!/:3.5.2)
    at com.sun.proxy.$Proxy187.selectByExample(Unknown Source) ~(na:na)
    at com.sportswin.soa.illidan.hub.service.impl.HubRoundPumpRecordService.lambda$page$0( ~(classes!/:na)
    at com.github.pagehelper.Page.doSelectPageInfo( ~(pagehelper-5.1.11.jar!/:na)
    at ~(classes!/:na)
    at com.sportswin.soa.illidan.hub.common.HubCommonUtil.getRoundPump( ~(classes!/:na)
    at com.sportswin.soa.illidan.hub.common.HubScheduleTask.generateImpl( ~(classes!/:na)
    at com.sportswin.soa.illidan.hub.common.HubScheduleTask.generateFilesTask( ~(classes!/:na)
    at com.sportswin.soa.illidan.hub.common.HubScheduleTask.lambda$generateExportFile$0( ~(classes!/:na)
    at java.util.concurrent.Executors$ ~(na:1.8.0_212)
    at ~(na:1.8.0_212)
    at com.sportswin.soa.misc.config.async.pool.MdcTaskDecorator.lambda$decorate$0( ~(soa-misc-1.0.0-SNAPSHOT.jar!/:na)
    at java.util.concurrent.ThreadPoolExecutor.runWorker( ~(na:1.8.0_212)
    at java.util.concurrent.ThreadPoolExecutor$ ~(na:1.8.0_212)
    at ~(na:1.8.0_212)
Caused by: com.mysql.cj.jdbc.exceptions.MySQLTimeoutException: Statement cancelled due to timeout or client request
    at com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException( ~(mysql-connector-java-8.0.19.jar!/:8.0.19)
    at com.mysql.cj.jdbc.StatementImpl.checkCancelTimeout( ~(mysql-connector-java-8.0.19.jar!/:8.0.19)
    at com.mysql.cj.protocol.a.NativeProtocol.sendQueryPacket( ~(mysql-connector-java-8.0.19.jar!/:8.0.19)
    at com.mysql.cj.NativeSession.execSQL( ~(mysql-connector-java-8.0.19.jar!/:8.0.19)
    at com.mysql.cj.jdbc.ClientPreparedStatement.executeInternal( ~(mysql-connector-java-8.0.19.jar!/:8.0.19)
    at com.mysql.cj.jdbc.ClientPreparedStatement.execute$original$zlbDqAbk( ~(mysql-connector-java-8.0.19.jar!/:8.0.19)
    at com.mysql.cj.jdbc.ClientPreparedStatement.execute$original$zlbDqAbk$accessor$77RRWgJE( ~(mysql-connector-java-8.0.19.jar!/:8.0.19)
    at com.mysql.cj.jdbc.ClientPreparedStatement$auxiliary$ Source) ~(mysql-connector-java-8.0.19.jar!/:8.0.19)
    at org.apache.skywalking.apm.agent.core.plugin.interceptor.enhance.InstMethodsInter.intercept( ~(skywalking-agent.jar:6.5.0)
    at com.mysql.cj.jdbc.ClientPreparedStatement.execute( ~(mysql-connector-java-8.0.19.jar!/:8.0.19)
    at ~(druid-1.1.9.jar!/:1.1.9)
    at ~(druid-1.1.9.jar!/:1.1.9)
    at ~(druid-1.1.9.jar!/:1.1.9)
    at ~(druid-1.1.9.jar!/:1.1.9)
    at ~(druid-1.1.9.jar!/:1.1.9)
    at ~(druid-1.1.9.jar!/:1.1.9)
    at ~(druid-1.1.9.jar!/:1.1.9)
    at org.apache.ibatis.executor.statement.PreparedStatementHandler.query( ~(mybatis-3.5.2.jar!/:3.5.2)
    at org.apache.ibatis.executor.statement.RoutingStatementHandler.query( ~(mybatis-3.5.2.jar!/:3.5.2)
    at org.apache.ibatis.executor.SimpleExecutor.doQuery( ~(mybatis-3.5.2.jar!/:3.5.2)
    at org.apache.ibatis.executor.BaseExecutor.queryFromDatabase( ~(mybatis-3.5.2.jar!/:3.5.2)
    at org.apache.ibatis.executor.BaseExecutor.query( ~(mybatis-3.5.2.jar!/:3.5.2)
    at sun.reflect.GeneratedMethodAccessor237.invoke(Unknown Source) ~(na:na)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke( ~(na:1.8.0_212)
    at java.lang.reflect.Method.invoke( ~(na:1.8.0_212)
    at org.apache.ibatis.plugin.Plugin.invoke( ~(mybatis-3.5.2.jar!/:3.5.2)
    at com.sun.proxy.$Proxy254.query(Unknown Source) ~(na:na)
    at sun.reflect.GeneratedMethodAccessor237.invoke(Unknown Source) ~(na:na)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke( ~(na:1.8.0_212)
    at java.lang.reflect.Method.invoke( ~(na:1.8.0_212)
    at org.apache.ibatis.plugin.Plugin.invoke( ~(mybatis-3.5.2.jar!/:3.5.2)
    at com.sun.proxy.$Proxy254.query(Unknown Source) ~(na:na)
    at com.github.pagehelper.util.ExecutorUtil.pageQuery( ~(pagehelper-5.1.11.jar!/:na)
    at com.github.pagehelper.PageInterceptor.intercept( ~(pagehelper-5.1.11.jar!/:na)
    at org.apache.ibatis.plugin.Plugin.invoke( ~(mybatis-3.5.2.jar!/:3.5.2)
    at com.sun.proxy.$Proxy254.query(Unknown Source) ~(na:na)
    at sun.reflect.GeneratedMethodAccessor263.invoke(Unknown Source) ~(na:na)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke( ~(na:1.8.0_212)
    at java.lang.reflect.Method.invoke( ~(na:1.8.0_212)
    at org.apache.ibatis.plugin.Invocation.proceed( ~(mybatis-3.5.2.jar!/:3.5.2)
    at tk.mybatis.orderbyhelper.OrderByHelper.intercept( ~(orderby-helper-0.0.2.jar!/:na)
    at org.apache.ibatis.plugin.Plugin.invoke( ~(mybatis-3.5.2.jar!/:3.5.2)
    at com.sun.proxy.$Proxy254.query(Unknown Source) ~(na:na)
    at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList( ~(mybatis-3.5.2.jar!/:3.5.2)
    at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList( ~(mybatis-3.5.2.jar!/:3.5.2)
    at sun.reflect.GeneratedMethodAccessor414.invoke(Unknown Source) ~(na:na)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke( ~(na:1.8.0_212)
    at java.lang.reflect.Method.invoke( ~(na:1.8.0_212)
    at org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke( ~(mybatis-spring-2.0.2.jar!/:2.0.2)
    ... 19 common frames omitted

performance – MySQL update query where in subquery slow

I’m having issues with MySQL query running extremely slow. It takes about 2 min for each UPDATE to process.

This is the query:

   UPDATE msn
   SET is_disable = 1
   WHERE mid IN
       SELECT mid from link
       WHERE rid = ${param.rid}

So my question is, I would like to know how the performance of the UPDATE statement will be affected if the result of the subquery is 0 or NULL. Because I think that maybe the process is slow because the result of the subquery is 0 or NULL.

Thanks a lot in advance.